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

On Friday, January 7, 2011 4:15:25 PM UTC+6, "Satish Burnwal (sburnwal)" wrote:

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:

Type count
-----------------------
FOOD 40 //10+15+15
SOAP 42 //20+10+12
PAPER 25

Thanks in advance,
-Satish

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,
I think you can use the sum / count keyword on find query. Follow the link
http://arafats.info/how-to-use-sum-on-cakephp/
Thanks
http://arafats.info