1
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?

  • 3
    Although not relevant for such a basic question, you should always add the tag for your DBMS (postgresql, oracle, ...) – a_horse_with_no_name Jun 8 '15 at 6:19
  • @a_horse_with_no_name it's not so basic (assuming I understand correctly they need some form of pivot, besides the aggregation.) – ypercubeᵀᴹ Jun 8 '15 at 7:58
  • @a_horse_with_no_name Even basic questions can get better answers if we know the exact DBMS. Each DBMS has unique features which can be used to solve even basic problems. – Colin 't Hart Jun 8 '15 at 8:25
  • @Colin'tHart: that's why I said the question should be tagged with the appropriate DBMS. – a_horse_with_no_name Jun 8 '15 at 8:26
  • @a_horse_with_no_name Yes: I'm taking issue with the first part of your sentence :-) – Colin 't Hart Jun 8 '15 at 8:40
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

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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