name number
=============
lin | 8
lin | 8
lin | 8
rob | 8
rob | 8
rob | 8
lin | 8
lin | 41
lin | 41
rob | 41
and I want the result like
name | Count of 8 | count of 41
====================================
lin | 4 | 2
rob | 3 | 1
Can anyone help me please?
2 Answers 2
This is know as conditional counting and can be achieved using a case
expression:
select name,
count(case when number = 8 then 1 end) as count_of_8,
count(case when number = 41 then 1 end) as count_of_41
from the_table
group by name
order by name;
Aggregate functions (like count()
) ignore NULL
values. The case
expression inside the count()
returns null for those values that we don't want to count, hence only the number of rows containing that value are counted. case when x then y end
is equivalent to case when x then y else null end
If using PostgreSQL 9.4+, the use of the new aggregate filter
clause is the most efficient way to do this:
select
name,
count(*) filter (where number = 8) as count_of_8,
count(*) filter (where number = 41) as count_of_41
from <table>
group by name;
postgresql
,oracle
, ...)