问题描述:

I am expecting the result of having count of 2 different columns values

Name | fruits

----------------

Vishal | orange

Manish | orange

Vishal | apple

Manish | orange

Manish | apple

Vishal | orange

Vishal | mango

Vishal | banana

Result should be

Name | Orange count | Apple count| mango | banana

--------------------------

Vishal | 2 | 1 | 1 | 1

Manish | 2 | 1 | 0 | 0

Another result should be

name | fruits

---------------

Vishal | orange, Apple , mango, banana

Manish | orange , Apple

网友答案:

You can use conditional aggregation for this:

select name, 
       count(case when fruits = 'orange' then 1 end) as orange_count, 
       count(case when fruits = 'apple' then 1 end) as apple_count, 
       count(case when fruits = 'mango' then 1 end) as mango_count, 
       count(case when fruits = 'banana' then 1 end) as banana_count
from the_table
group by name;

Some DBMS also support the ANSI SQL filter clause which makes this a bit more readable:

select name, 
       count(*) filter (where fruits = 'orange') as orange_count, 
       count(*) filter (where fruits = 'apple') as apple_count, 
       count(*) filter (where fruits = 'mango') as mango_count, 
       count(*) filter (where fruits = 'banana') as banana_count
from the_table
group by name;
网友答案:

Here is a generic pivot query which should work across most RDBMS:

SELECT Name,
       SUM(CASE WHEN fruits = 'orange' THEN 1 ELSE 0 END) AS orange_count,
       SUM(CASE WHEN fruits = 'apple'  THEN 1 ELSE 0 END) AS apple_count,
       SUM(CASE WHEN fruits = 'mango'  THEN 1 ELSE 0 END) AS mango_count,
       SUM(CASE WHEN fruits = 'banana' THEN 1 ELSE 0 END) AS banana_count
FROM yourTable
GROUP BY Name

If you are using SQL Server, Oracle, or Postgres, there are built-in PIVOT functions which can simplify this and possibly improve performance as well.

相关阅读:
Top