Results 1 to 13 of 13

Thread: Grouping

  1. #1
    Join Date
    Dec 2011
    Posts
    8

    Question Unanswered: Grouping

    i have result like:
    q_id|q_name|q_money
    0 | JAMES | 500
    1 | JAMES | 500
    2 | JAKE | 100
    3 | JOHN | 1000
    4 | JOHN | 2000

    and i want this result to be like this,
    grouped my names and money is summed.
    q_id|q_name|q_money
    ? | JAMES | 1000
    ? | JAKE | 100
    ? | JOHN | 3000

    ( ? -- not sure what the ID should be)

    I take data from two different tables, and my query looks like:
    SELECT q_id, q_name, q_money
    FROM (SELECT aa_id q_id, aa_name q_name, aa_money q_money FROM table_aa) as aa
    UNION SELECT q_id, q_name, q_money
    FROM (SELECT bb_id q_id, bb_name q_name, bb_money q_money FROM table_bb) as bb


    Table_aa columns are: aa_id, aa_name, aa_money
    Table_bb columns are: bb_id, bb_name, bb_money


    How my query should look like, to get the "grouped and summed" result?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ROUND(AVG(q_id),0) AS q_id
         , q_name
         , SUM(q_money) AS q_money
      FROM ( SELECT aa_id    AS q_id
                  , aa_name  AS q_name
                  , aa_money AS q_money 
               FROM table_aa
             UNION ALL
             SELECT bb_id 
                  , bb_name 
                  , bb_money 
               FROM table_bb ) AS u
    GROUP
        BY q_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    8
    Thank you, but i think i just found a bit easier query
    Code:
    SELECT
    q_name, sum(q_summa)
    FROM
    (
    SELECT *
    FROM (SELECT aa_id q_id, aa_name q_name, aa_money q_money FROM table_aa) as aa
    UNION SELECT *
    FROM (SELECT bb_id q_id, bb_name q_name, bb_money q_money FROM table_bb) as bb
    ) as q
    GROUP BY q_name
    But thank you anyway for quick response Other postgre forums never reply so quicly.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by vvalter View Post
    Other postgre forums never reply so quicly.
    Maybe because you are using the wrong product name? It's either Postgres or PostgreSQL, never postgre

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vvalter View Post
    Thank you, but i think i just found a bit easier query
    the operative word being "think"

    what makes you think yours is "easier" than mine?

    also, yours has a potential bug in that you used UNION instead of UNION ALL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2011
    Posts
    8
    Quote Originally Posted by shammat View Post
    Maybe because you are using the wrong product name? It's either Postgres or PostgreSQL, never postgre
    i meant actually postgreSQL forums

    r937;
    i dont say it IS easier. it's easier to me, because i wrote that query by myself ..
    thanks for the bug report.

    EDIT:
    actually your query is exactly same as mine, formatting is just a different of mine.
    Last edited by vvalter; 12-06-11 at 09:04.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vvalter View Post
    EDIT:
    actually your query is exactly same as mine
    no, it isn't, sorry

    but if you're happy, that's the main thing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2011
    Posts
    8
    it does the same result, expect the first SELECT....

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you understand the difference between UNION and UNION ALL?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2011
    Posts
    8
    actually .. no ..

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, that's how your query and my query are different

    maybe you should find out, although it probably doesn't matter for this case, but eventually you are gonna write a union where the difference will be important

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2011
    Posts
    8
    well, i changed UNION to UNION ALL :P

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    excellent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •