Postgresql count based on condition

Question

I am executing this query

SELECT COUNT(designer_orders.id) as pr, 
       SUM(IF designer_orders.package_status == 'packed' THEN 0 ELSE 1 END) as pu 
FROM "designer_orders"

Expected output

PR PU
10 5

Getting Output

PR PU
10 0

This block of query not working as expected

SUM(IF designer_orders.package_status == 'packed' THEN 0 ELSE 1 END)

That query will not work at all because SQL does not have an IF statement. You need to use CASE — user330315
– user330315, Commented Mar 12, 2014 at 10:53
@a_horse_with_no_name i just saw if in postgresql documentation. by the way how do i use 'CASE' — chirag7jain
– chirag7jain, Commented Mar 12, 2014 at 10:56

user330315 · Accepted Answer · 2014-03-12 11:02:10Z

4

There is no IF in SQL, you need to use CASE.

Additionally the equality operator in SQL is = not ==

SELECT COUNT(id) as pr, 
       SUM(CASE WHEN package_status = 'packed' THEN 0 ELSE 1 END) as pu 
FROM designer_orders;

For an introduction into SQL, please see this chapter of the manual: http://www.postgresql.org/docs/current/static/tutorial-sql.html

answered Mar 12, 2014 at 11:02

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

CASE WHEN should be used here

James · Accepted Answer · 2014-03-12 11:25:54Z

3

Try this:

SELECT COUNT(designer_orders.id) as pr, SUM(CASE WHEN designer_orders.package_status = 'packed' THEN 0 ELSE 1 END) as pu FROM "designer_orders"

James

5,1697 gold badges43 silver badges80 bronze badges

answered Mar 12, 2014 at 11:06

fabriziomello

661 silver badge2 bronze badges