Postgres Pro
Downloads
Home   >   mailing lists

Thread: Grouping and aggregates

Grouping and aggregates

From
"Peter Darley"
Date:
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

Re: Grouping and aggregates

From
Joel Burton
Date:
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

Re: Grouping and aggregates

From
"Peter Darley"
Date:
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