| From: | Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Merging two GROUP BY-queries |
| Date: | 2009-11-26 01:22:32 |
| Message-ID: | d9e88eaf0911251722x40f2c96tf3928f574f5a7df8@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
Consider these two separate queries that returns the amount of shots -
periodwise - for a hockey team over a season:
gik=# SELECT period_num, sum(shots_teama) AS shots_home
gik-# FROM periods
gik-# WHERE matchid in (SELECT id
gik(# FROM matches
gik(# WHERE home AND played AND origin=1 AND
match_date between '2009-08-01' AND now()::date AND NOT training)
gik-# GROUP BY period_num
gik-# ORDER BY period_num;
period_num | shots_home
------------+------------
1 | 113
2 | 114
3 | 119
(3 rows)
gik=# SELECT period_num, sum(shots_teamb) AS shots_away
gik-# FROM periods
gik-# WHERE matchid in (SELECT id
gik(# FROM matches
gik(# WHERE NOT home AND played AND origin=1 AND
match_date between '2009-08-01' AND now()::date AND NOT training)
gik-# GROUP BY period_num
gik-# ORDER BY period_num;
period_num | shots_away
------------+------------
1 | 89
2 | 120
3 | 110
(3 rows)
I would like to join these two queries into a single result, but I'm
not sure how to accomplish this. I was thinking about an INNER JOIN
USING (period_num) but I don't know how to specify the query (or if
it's possible at all):
=> SELECT (SELECT ...query for shots_home...) INNER JOIN (SELECT
...query for shots_away...)
ERROR: syntax error at or near "INNER"
LINE 7: ORDER BY period_num) INNER JOIN (SELECT period_num, sum(shot...
^
Is there a way to merge these two queries into one result?
--
- Rikard
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mark Styles | 2009-11-26 01:48:45 | Re: Merging two GROUP BY-queries |
| Previous Message | Rikard Bosnjakovic | 2009-11-26 01:14:04 | Re: Column aliases for GROUP BY and HAVING |