CODEWITHSUNDEEP
sqloracle-database
thewaleed100
thewaleed100

Reputation: 1

Add values from count that have come from a UNION

I am attempting to add together the values_occurrence for the id's that match. For example ID number 1 has 3 and another record shows ID number 1 having 4. I want to ADD 3 + 4 based on the ID matching.

I am trying to see which ID has the most entries in each table and then add them together. 

ID  value_occurrence
--------------------
1    3
1    4

so far this is what I have. 

SELECT ID, COUNT(ID) AS value_occurrence 
FROM     TABLE1
GROUP BY ID
UNION 
SELECT ID, COUNT(ID) AS value_occurrence 
FROM     TABLE2
GROUP BY ID
ORDER BY ID ASC;

Any help would be appreciated.

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Do a union all and then aggregate:

select id, count(*) as total_cnt, sum(t1) as t1_cnt, sum(t2) as t2_cnt
from ((select id, 1 as t1, 0 as t2 from table1) union all
      (select id, 0, 1 from table2)
     ) t
group by id
order by id;

Upvotes: 2

Related Questions