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.

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 27, '09 at 7:05p
activeNov 27, '09 at 7:05p
posts1
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Rikard Bosnjakovic: 1 post

People

Translate

site design / logo © 2022 Grokbase