From: | "Peter Darley" <pdarley(at)kinesis-cem(dot)com> |
---|---|
To: | "Joel Burton" <joel(at)joelburton(dot)com> |
Cc: | "Pgsql-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Grouping and aggregates |
Date: | 2002-06-04 18:32:49 |
Message-ID: | NNEAICKPNOGDBHNCEDCPOEHNCJAA.pdarley@kinesis-cem.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Joel,
That's excellent! This list rocks.
One thing that is slightly wrong is that it returns rows with no count last
(null) rather than first (0) which is fixable with a coalesce:
SELECT *
FROM code_list
LEFT JOIN ( SELECT codeid,
count(*) AS codecount
FROM codes
GROUP BY codeid )
AS codes
ON code_list.id = codes.codeid
ORDER BY coalesce(codecount, 0);
Since I don't need the count, I could also just use a sub-select in the
ORDER BY:
SELECT *
FROM code_list
ORDER BY ( SELECT count(*)
FROM codes
WHERE codeid=code_list.id );
Thanks,
Peter Darley
-----Original Message-----
From: Joel Burton [mailto:joel(at)joelburton(dot)com]
Sent: Tuesday, June 04, 2002 11:08 AM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Grouping and aggregates
On Tue, 4 Jun 2002, Peter Darley wrote:
> Friends,
> I've got the following query, which doesn't work because you apparently
> can't group by table.*. I was wondering if there was any way to write
this
> without having to have every field listed in the GROUP BY?
>
> My query:
> SELECT code_list.* FROM code_list LEFT JOIN codes ON
> code_list.id=codes.codeid GROUP BY code_list.* ORDER BY Count(codes.id);
Would this work?
SELECT *
FROM code_list
LEFT JOIN ( SELECT codeid,
count(*) AS codecount
FROM codes
GROUP BY codeid )
AS codes
ON code_list.id = codes.codeid
ORDER BY codecount;
--
Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | Booth, Robert | 2002-06-04 19:20:02 | Lost Access To Table |
Previous Message | Joel Burton | 2002-06-04 18:07:58 | Re: Grouping and aggregates |