| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-06-11, 03:43
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 8
|
|
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?
|
|
12-06-11, 05:13
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
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
|
|
12-06-11, 05:32
|
|
Registered User
|
|
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.
|
|
12-06-11, 06:01
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by vvalter
Other postgre forums never reply so quicly.
|
Maybe because you are using the wrong product name? It's either Postgres or PostgreSQL, never postgre 
|
|
12-06-11, 06:13
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
Quote:
Originally Posted by vvalter
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
|
|
12-06-11, 07:48
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 8
|
|
Quote:
Originally Posted by shammat
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 08:04.
|
12-06-11, 08:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
Quote:
Originally Posted by vvalter
EDIT:
actually your query is exactly same as mine
|
no, it isn't, sorry
but if you're happy, that's the main thing
|
|
12-06-11, 09:34
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 8
|
|
it does the same result, expect the first SELECT....
|
|
12-06-11, 09:49
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
do you understand the difference between UNION and UNION ALL?
|
|
12-06-11, 09:53
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 8
|
|
|
|
12-06-11, 10:01
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
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
|
|
12-07-11, 01:17
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 8
|
|
well, i changed UNION to UNION ALL :P
|
|
12-07-11, 05:48
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
|
|
excellent
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Grouping
