CODEWITHSUNDEEP
sqljoincount
Martina Zapletalová
Martina Zapletalová

Reputation: 359

join and count occurrence

I'm new to SQL and I have two tables like this

tab1:

+---+---+
| ID|som|
+---+---+
|  e|  1|
|  d|  j|
|  c|  1|
|  b|  1|
|  a|  p|
+---+---+

tab2:

+------+---+
|SK_CUR|som|
+------+---+
|     b|  d|
|     a|  c|
|     a|  i|
+------+---+

and I just want to count occurrence of letters in both tables. So the output should look like this:

+------+---+
|    ID| oc|
+------+---+
|     a|  3|
|     b|  2|
|     c|  1|
|     d|  1|
|     e|  1|
+------+---+

Upvotes: 0

Views: 49

Answers (2)

Rajendra Kalepu
Rajendra Kalepu

Reputation: 97

try the below query, 

select ID , count(*) from (
select ID from tab1 where like '%[a-z]%'
union all
select som as ID from tab1 where som like '%[a-z]%'
union all
select SK_CUR as ID from tab2 where SK_CUR like '%[a-z]%'
union all
select som as ID from tab2 where som like '%[a-z]%')
group by ID

Since you need only letters from all the columns of both tables, i have used the filter like '%[a-z]%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you just want union all and group by:

select id, count(*)
from ((select id from t1) union all
      (select SK_CUR from t2)
     ) t
group by id;

Upvotes: 2

Related Questions