From: "Simon Kinsella" <simon@bluefiresystems.co.uk>
To: <pgsql-sql@postgresql.org>
Subject: GROUPing only those rows that do not contain a NULL field?
Date: Fri, 11 Feb 2005 20:44:01 -0000
Organization: Bluefire Systems Ltd
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcUQemSGzfFqZBYFTmim4goThJ7+6g==
Message-Id: <20050211204404.21ECE8BA15C@svr1.postgresql.org>

Hi

I wonder if anyone can help me with a SELECT / GROUP BY problem I'm having.
I'm trying to refine a query so that my GROUP BY clause only aggregates rows
that have a non-NULL value in one of the fields, leaving other rows
'ungrouped'.

An example table, resulting from a LEFT JOIN and subselect on three tables,
might be (before grouping) :

SELECT  user_group_id, user_id, topic_id FROM user_groups NATURAL JOIN users
LEFT JOIN
(SELECT user_id, topic_id FROM topic_participants WHERE topic_id = 567) AS a
USING (user_id)


user_group_id   |  user_id   |   topic_id
-----------------------------------------
1                  101           NULL       
1                  102           567
1                  103           567
2                  101           NULL
2                  106           567
3                  101           NULL
3                  104           567
3                  102           567
4                  103           567
4                  104           567
5                  105           NULL
6                  103           567
6                  104           567

(topic id is either a single value, or NULL if the user is not part of the
topic)

For UI reasons I would like this result collapsed thus:

user_group_id   |  user_id   |   topic_id
-----------------------------------------
1                  101           NULL       
1                  102           567
1                  103           567
2                  101           NULL
2                  106           567
3                  101           NULL
3                  104           567
5                  105           NULL

Note that for all rows where topic_id IS NOT NULL, only one row per user_id
is returned (user_group_id is not relevant for these rows).

Can this aggregation be achieved with a DISTINCT ON / GROUP BY clause? (Or
anything else). I haven't managed to find anything that leaves the
NULL-field rows unscathed so far.

Thanks in advance for any help!

Simon K