Обсуждение: Merging two GROUP BY-queries

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

On Thu, Nov 26, 2009 at 02:22:32AM +0100, Rikard Bosnjakovic wrote:
> 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):

Something like this:

SELECT period_num
     , SUM(CASE WHEN home THEN shots_teama ELSE 0) AS shots_home
     , SUM(CASE WHEN away THEN shots_teamb ELSE 0) AS shots_away
FROM   periods
     , matches
WHERE  period.matchid = matches.id
AND    matches.played
AND    matches.origin = 1
AND    matches.match_date between '2009-08-01' AND now()::date
AND    NOT matches.training
GROUP BY period_num
ORDER BY period_num;

--
Mark
http://www.lambic.co.uk

On Thu, Nov 26, 2009 at 02:48, Mark Styles <postgres@lambic.co.uk> wrote:

> Something like this:
[...]

Works perfect. Thank you.

--
- Rikard - http://bos.hack.org/cv/