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

Extracting special case from a player table without union

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Extracting special case from a player table without union
Date: 2009-11-27 19:05:15
Message-ID: d9e88eaf0911271105w7dd9816fnd62a7e1c56b6a510@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I'm running a hockey player database and I'm building up statistics
about player penalties. I extract the penalties like this:

SELECT P.playerid, T.number, count(P.playerid) AS num_penalties,
sum(P.length) AS length_penalties
FROM period_penalties P, troops T
WHERE T.origin=1 AND T.season=2009 AND T.playerid=P.playerid AND NOT
P.opponent AND P.period_id IN (...id_list...)
GROUP BY P.playerid, T.number

and get a result like this:

 playerid | number | num_penalties | length_penalties
----------+--------+---------------+------------------
      236 |     89 |             9 |               26
      267 |     20 |             8 |               37
      214 |     14 |             1 |                2
...

This is what I want.

However, there is a special case which I need to fetch as well. The
special case is lines in the table with playerid = 0, which means it's
a team penalty instead of a player penalty. Id 0 does not exist in the
player table (called "troops" in the query above), and that's why that
row does not get included in the result.

The only way I found out how to include the special case row is by
doing a union-query, like this:

SELECT ...as above...
UNION
SELECT P.playerid, Null, count(P.playerid) AS num_penalties,
sum(P.length) AS length_penalties
FROM period_penalties P
WHERE playerid=0 AND NOT P.opponent AND P.period_id IN (...period_ids...)
GROUP BY P.playerid
ORDER BY length_penalties DESC, num_penalties DESC

and the result is like this:

 playerid | number | num_penalties | length_penalties
----------+--------+---------------+------------------
       20 |      6 |            15 |              103
       28 |      4 |            11 |               46
      267 |     20 |             8 |               37
      265 |      2 |             5 |               31
        0 |        |             4 |               29
      122 |     11 |             4 |               29
...

Which is the actual result I want. However, I find the union-query
horrible and in a way somewhat redundant. But since my JOIN-skills are
too low I am unable to find a better solution than this.

If anyone can shed some light on how to approach this problem better
than my solution I'm all ears.


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

pgsql-novice by date

Next:From: richard terryDate: 2009-11-27 23:50:44
Subject: Re: bytea and text
Previous:From: Jean-Yves F. BarbierDate: 2009-11-27 15:18:55
Subject: views

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