Thread: Query to find sum of grouped counts from 2 tables
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
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