PostgreSQL group by then count by value

Question

I have the following data

id | sub_id |status |
---|--------|-------|
1  |    1   | new   |
2  |    2   | old   |
3  |    2   | new   |
4  |    3   | old   |

Which query should I use to get the following result? I want to group the result by sub_id and then add new columns that store the number of statuses of the corresponding sub_id.

sub_id | new  | old  | total |
-------|------|------|-------|
1      |  1   | 0    |  1    |
2      |  1   | 1    |  2    |
3      |  0   | 1    |  1    |

I tried this and it did not work as expected.

SELECT 
  sub_id, 
  count(status='new') AS new, 
  count(status='old') AS old,
  count(status) AS total
FROM table
GROUP BY sub_id;

I'm glad it does. For the future: Always tag SQL requests with the DBMS. And "it did not work as expected" is a very bad way to tell us about the issue. Please tell us in what way this does not work. Do you get a syntax error? Are the figures too high or too low? Do you get more or less rows than expected? Don't let us guess. (Though guessing was easy here :-) — Thorsten Kettner
– Thorsten Kettner, Commented Sep 3, 2020 at 10:20
By"it did not work as expected" I meant unexpected output, which you explain to me, thanks. — slick
– slick, Commented Sep 3, 2020 at 10:25

Thorsten Kettner · Accepted Answer · 2020-09-03 10:23:01Z

1

status = 'new' is

true for all rows with status = 'new'
false for all rows with status <> 'new'
null for all rows with status is null.

COUNT( <expression> ) counts all non-null occurences of the expression. This means you count both 'new' and 'old', as neither true nor false is null, when you only want to count 'new'. Use a CASE expression instead:

count(case when status = 'new' then 1 end)

which is short for

count(case when status = 'new' then 1 else null end)

or the same with SUM:

sum(case when status = 'new' then 1 else 0 end)

Some DBMS (MySQL for instance) treat true as 1 and false as 0. There you can even use:

sum(status = 'new')

In PostgreSQL you can also use the filter() clause:

count(*) filter (where status = 'new')

edited Sep 3, 2020 at 10:23

user330315

answered Sep 3, 2020 at 10:13

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

You left out sum( (status = 'new')::int ), which is Postgres specific and still quite useful (if you are using avg() for instance).

Collectives™ on Stack Overflow

PostgreSQL group by then count by value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related