General
Highlighted
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:

A

A

A

B

C

2nd table T2 has below value

A

B

D

E

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

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.

Accepted Solutions

## 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
from tabA
union all
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
from tabB
group by 1
) as dt
group by 1```

1 ACCEPTED SOLUTION
3 REPLIES 3

## 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
from tabA
union all
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
from tabB
group by 1
) as dt
group by 1```

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
;```