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

Search Discussions

  • Thomas Kellerer at Jan 7, 2011 at 10:29 am

    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
  • Arafatix at Jul 12, 2012 at 6:33 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 7, '11 at 10:25a
activeJul 12, '12 at 6:33p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase