ayush varshney ayush varshney -4 years ago 94
SQL Question

Sql conditional count

Can anyone help me with the sql and sqlite query for the below condition. To be specific, i wanted to show the merged count of 2 categories.

**Table**
Category Sub_category
A 1
A 2
A 2
B 1
C 1
C 1
C 2
D 1
D 1
D 1
D 2
D 3
**Required Output**
Category Sub_category Count **condition(not part of o/p just instruction)**
A 1 1 -
A 2+ 2 -
B 1 1 -
C 1 2 -
C 2+ 1 -
D 1 3 -
D 2+ 1 should contain the count of 2 and more


i am able to achieve the same using below:

select Category,
count(CASE WHEN Sub_category = 1 THEN Sub_category END) AS '1',
count(CASE WHEN Sub_category >= 2 THEN Sub_category END) AS '2+'
from table


however output is little different hence looking for the first output only.

**Output**
Category 1 2+
A 1 2
B 1 0
C 1 2
D 3 2


Thanks in advance!

Answer Source

You want:

select Category,
       (case when sub_category = 1 then '1' else '2+' end) as sub_category,
       count(*)
from table
group by Category,
         (case when sub_category = 1 then '1' else '2+' end);

That is, you want to put the sub category groups on rows, not as columns.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download