Geek Answers Handbook

SQL: counting value values

Let be

user | fruit
------------
1    | apple
1    | apple
1    | apple
2    | apple
2    | apple
1    | pear

Attempt to combine countand group byto get

user | apples | pears
---------------------
1    | 3      | 1
2    | 2      | 0

Any clues on how to proceed are appreciated.

+4
source share
2 answers

Use expressions caseto do conditional counts:

select user,
       count(case when fruit = 'apple' then 1 end) as apples,
       count(case when fruit = 'pear' then 1 end) as pears
from tablename
group by user
+6
source

If you are working on Oracle, you should use the PIVOT function:

SELECT *
  FROM fruit t
 PIVOT (COUNT(fruit) AS cnt 
          FOR(fruit) IN ('apple' AS apple
                       , 'pear' AS pear) );

More details and full samples on PIVOT / UNPIVOT that you will find on the Internet (for example, here https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1 )

+1
source

More articles:


All Articles