current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm migrating a site from mysql to PostgreSQL (which I really like so far) but I have a question regarding a certain query. Please note that this was not a stored function- this was a string that was passed in manually in a python script(I am aware this is a terrible practice, but it's for an internal app only). In mysql, the query was structured like so:

SELECT 
  name,
  SUM(IF(is_correct, 1, 0)) AS correct,
  SUM(IF(is_correct, 0, 1)) AS wrong
FROM
  k_p AS p JOIN
  k_v AS v ON (variable_id=v.id)
WHERE
  content_type_id=%s %s %s
GROUP BY
  v.id
ORDER BY sort_order

is_correct is just a column with boolean data, either a 0 or 1. I realize you can't do straight IFs in postgres as you would in mysql. The documentation seems to suggest that the preferably conversion would be to use a case/when statement. I have experimented with no success. The SUM/IF statements are what are throwing me off- the rest of the query should be fine. Any advice would be greatly appreciated!

BONUS Question: How would you do it if one of the queries included:

 SUM(IF(ISNULL(is_correct), 0, 1)) AS wrong
share|improve this question

2 Answers 2

up vote 4 down vote accepted

You want to you the CASE construct:

SELECT 
  name,
  SUM(case when is_correct = 1 then 1 else 0 end) AS correct,
  SUM(case when is_correct = 1 then 0 else 1 end) AS wrong
FROM
  k_p AS p JOIN
  k_v AS v ON (variable_id=v.id)
WHERE
  content_type_id=%s %s %s
GROUP BY
  v.id
ORDER BY sort_order
share|improve this answer
    
This looks promising! Just for testing purposes, I ran "SELECT SUM(case is_correct when 1 then 1 else 0 end) AS correct FROM k_p" and got the error "ERROR: operator does not exist: boolean = integer" with an arrow until the when. Any idea what's going on? –  i_thought_i_was_smart Jun 11 '12 at 17:15
    
@i_thought_i_was_smart Just updated the code, it should work now –  Adrian Carneiro Jun 11 '12 at 17:18
    
Nice! I think I came up with an alternate solution- "SELECT SUM(case (cast (is_correct as integer)) when 1 then 1 else 0 end) FROM k_p"- seems to work. Which way is the more 'correct' way? –  i_thought_i_was_smart Jun 11 '12 at 17:21
    
@i_thought_i_was_smart: if is_correct is really a boolean column than you can use case when is_correct then 1 else 0 end –  a_horse_with_no_name Jun 11 '12 at 19:26
up vote 1 down vote 
select name, correct, wrong
from (
    SELECT 
      id,
      name,
      count(nullif(is_correct, false)) AS correct,
      count(nullif(is_correct, true)) AS wrong
    FROM
      k_p AS p JOIN
      k_v AS v ON (variable_id=v.id)
    WHERE
      content_type_id=%s %s %s
    GROUP BY
      v.id, name
)
ORDER BY sort_order
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.