How to count occurrence of different values contained in a column ?

General
K_Y
Enthusiast

How to count occurrence of different values contained in a column ?

Hi 

I've a requirement where I have two table suppose T1 and T2, each table has one column named as grade. Suppose First table has value as below:

GRADE

A

A

A

B

C

 2nd table T2 has below value

GRADE

A

B

D

E

 

So in my output i should get the occurence of each value from both the table

GRADE COUNT

A              4

B              2

C              1

D              1

E              1

 

For a small dataset I can count this using case statement , but I want to find a way(kind of automation) which can deal with larger dataset where writing a case statement for each value is not possible.

 

Can anyone please help me on this.

 


Accepted Solutions
Ambassador

Re: How to count occurrence of different values contained in a column ?

If the number of rows per value is quite small you can use a simple UNON before counting:

select grade, count(*)
from 
 (
select grade from tabA union all select grade from tabB ) as dt group by 1

Otherwise you better use nested aggegation:

select grade, sum(cnt)
from 
 ( select grade, count(*) as cnt
   from tabA
   group by 1
   union all
   select grade, count(*) as cnt
   from tabB
   group by 1
 ) as dt
group by 1

 

1 ACCEPTED SOLUTION
3 REPLIES 3
Ambassador

Re: How to count occurrence of different values contained in a column ?

If the number of rows per value is quite small you can use a simple UNON before counting:

select grade, count(*)
from 
 (
select grade from tabA union all select grade from tabB ) as dt group by 1

Otherwise you better use nested aggegation:

select grade, sum(cnt)
from 
 ( select grade, count(*) as cnt
   from tabA
   group by 1
   union all
   select grade, count(*) as cnt
   from tabB
   group by 1
 ) as dt
group by 1

 

K_Y
Enthusiast

Re: How to count occurrence of different values contained in a column ?

Thank you so much for the help dnoeth , your queries are giving me desired output , however I also tried in below way and produced same output,  Could you please let me know if we can use below approach as well ?

select grade,count( case when grade='A' then 1 else 0 end)  as count1
FROM(select * from  tabA
UNION ALL
select * from  tabB) drv
group by 1
;

 

Ambassador

Re: How to count occurrence of different values contained in a column ?

COUNT counts non-NULL rows and this case when grade='A' then 1 else 0 end always returns a value (0 or 1), thus it's just a more complicated way to do a COUNT(*) :-)