Re: Query to find sum of grouped counts from 2 tables

Satish Burnwal (sburnwal) wrote on 07.01.2011 11:15:
> I have 2 tables containing the data for same items:
>
> STORE1
> -----------------------------
> Id type items
> -----------------------------
> 1 FOOD 10
> 2 FOOD 15
> 3 SOAP 20
>
> STORE2
> -----------------------------
> Id type items
> -----------------------------
> 1 FOOD 15
> 3 SOAP 10
> 4 PAPER 25
> 5 SOAP 12
>
>
> What I am looking for is one single query that would return me TYPE-wise
> total number of items from both the tables. UNION does not help me. I
> want the result as:

Hmm, I don't see why UNION shouldn't work:

SELECT type, sum(items) as count
FROM (
SELECT type, items
FROM store1
UNION ALL
SELECT type, items
FROM store2
) t
GROUP BY type