Re: Grouping and aggregates

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@joelburton.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@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant