Count Category From Column

Question

I have some table, which I want count column that column have 2 category, the explain table like this:

I have query with union like this:

select CLASS, BRANCH, COUNT(STATUS) as STATUS
from dwh.Table a
where STATUS = 'INCLUDE'
group by CLASS, BRANCH, STATUS
order by CLASS

union

select CLASS, BRANCH, COUNT(STATUS) as STATUS
from dwh.Table a
where STATUS = 'NOT INCLUDE'
group by CLASS, BRANCH, STATUS
order by CLASS

but I have got some error.

*"but I have got some error"* And what is that error? Why are both aggregating *and* grouping on the column `STATUS`? Normally you would aggregate *or* group on the column. — Thom A, Feb 16 '22 at 09:45
I'm so sorry for my mistake. but the image can provide complete my question in detail. thanks before — Oki Kuswanda, Feb 16 '22 at 09:53
No, the image only frustrates people who want to help you, because now we have to type over the information in the image into our test table, and when you provide the same information as text we can copy/paste. The same information as text will not only provide us with the same complete information to complete your question, it will also make live easier for us to help you — GuidoG, Feb 16 '22 at 10:13

score 1 · Accepted Answer · answered Feb 16 '22 at 09:46

I think you want conditional aggregation here:

SELECT
    CLASS,
    BRANCH,
    COUNT(CASE WHEN STATUS = 'INCLUDE'     THEN 1 END) AS [INCLUDE],
    COUNT(CASE WHEN STATUS = 'NOT INCLUDE' THEN 1 END) AS [NOT INCLUDE]
FROM dwh.Table
GROUP BY
    CLASS,
    BRANCH;

If you just want to report classes only, then simply group by the class by itself (and also select only the class):

SELECT
    CLASS,
    COUNT(CASE WHEN STATUS = 'INCLUDE'     THEN 1 END) AS [INCLUDE],
    COUNT(CASE WHEN STATUS = 'NOT INCLUDE' THEN 1 END) AS [NOT INCLUDE]
FROM dwh.Table
GROUP BY
    CLASS;

GuidoG · Answer 2 · 2022-02-16T11:00:17.427

A simple group by and count can do that

select t.class,
       count(case when t.status = 'INCLUDE' then 1 end) as INCLUDE,
       count(case when t.status = 'NOT INCLUDE' then 1 end) as [NOT INCLUDE]
from   test t
group by t.class

Test it yourself in this DBFiddle

That will leave a warning though

Warning: Null value is eliminated by an aggregate or other SET operation.

So another way to do it is group by and sum and this way you will not get a warning anymore

select t.class,
       sum(case when t.status = 'INCLUDE' then 1 else 0 end),
       sum(case when t.status = 'NOT INCLUDE' then 1 else 0 end)
from   test t
group by t.class

See the altered DBFiddle

Count Category From Column

2 Answers2