Re: 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