Re: Grouping and aggregates

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);

Thanks,
Peter Darley

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

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