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