Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm transferring over some queries from MySQL to PostgreSQL and I'm stumped on how to rewrite the following query to work in PostgreSQL: SUM(phoneid IN (1, 2, 6, 8)) AS completedcalls

I originally thought I could just do SUM(SELECT phoneid FROM myTable WHERE phoneid = 1 OR phoneid = 2 etc etc, but I do not believe you can have a SELECT within a sum.

I also tried using a WITH query but had no luck getting that to work.

share|improve this question
add comment (requires an account with 50 reputation)

3 Answers

up vote 4 down vote accepted

how about using CASE

SUM(CASE WHEN phoneid IN (1, 2, 6, 8) THEN 1 ELSE 0 END)
share|improve this answer
add comment (requires an account with 50 reputation)
up vote 4 down vote 
count(phoneid in (1,2,6,8) or null)
share|improve this answer
1  
It took me a while to figure out why this works, so probably worth an explanation: the phoneid in (1,2,6,8) evaluates to a boolean; if it's true, the or shortcuts, and gives true; if it's false, you have false or null, which evaluates to null; finally count() counts only the non-null elements, i.e. those that evaluated to true. I never knew you could do that with or. – IMSoP 21 hours ago
1  
@IMSoP Good explanation but I'm not sure the or shortcuts when true or if it evaluates all the expressions and applies the truth table – Clodoaldo Neto 20 hours ago
This is definitely the "least typing" solution. – ebyrob 20 hours ago
@ebyrob I like to say the the cleaner. – Clodoaldo Neto 20 hours ago
@ClodoaldoNeto Yes, I guess you're right, this isn't shortcutting as such - null or true also works, as that truth table suggests. – IMSoP 19 hours ago
add comment (requires an account with 50 reputation)
up vote 2 down vote

bool may be cast to integer:

SUM(CAST(phoneid IN (1, 2, 6, 8) AS INTEGER)) AS completedcalls
share|improve this answer
This is a useful trick to understand as it can be used for other aggregates which aren't defined on boolean, e.g. SELECT max(some_bool_column::integer)::boolean will give true if at least one row is true, false otherwise. – IMSoP 20 hours ago
2  
@IMSoP You can use bool_and() and bool_or() to aggregate boolean values. – Igor Romanchenko 20 hours ago
@IgorRomanchenko Ooh, I didn't know that! That's much more readable. :) – IMSoP 19 hours ago
add comment (requires an account with 50 reputation)

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.