Skip site navigation (1) Skip section navigation (2)

Re: Merging two GROUP BY-queries

From: Mark Styles <postgres(at)lambic(dot)co(dot)uk>
To: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Merging two GROUP BY-queries
Date: 2009-11-26 01:48:45
Message-ID: 20091126014845.GA23709@lambic.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: APseudoUtopiaDate: 2009-11-26 03:51:16
Subject: Re: Internal time stamps?
Previous:From: Rikard BosnjakovicDate: 2009-11-26 01:22:32
Subject: Merging two GROUP BY-queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group