I have often asked myself why the group by syntax is so ineffective.

Couldnt a short hand (non standard) addition be

Select grouped Description, count(Id) Count

All that is required is a new keyword, that internally picks all non aggregate columns and adds them to a group by statement.

The obvious benefit would of course appear when we have a large number of those non aggregate columns.

/M

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> That's one of several possible options for fields that only have one possible value, although you would need to alias the aggregate result if you want field names to appear:
>
> SELECT ep.PROJ_ID, MAX(p.PROJ_NAME) as PROJ_NAME, MAX(p.PRODUCT) as PRODUCT, count(*)
> FROM EMPLOYEE_PROJECT ep
> JOIN PROJECT p on ep.PROJ_ID = p.PROJ_ID
> GROUP BY ep.PROJ_ID
>
> From the top of my head, I can think of two other options that I would be more likely to use (although I'd expect the same result):
>
> SELECT ep.PROJ_ID, p.PROJ_NAME, p.PRODUCT, count(*)
> FROM EMPLOYEE_PROJECT ep
> JOIN PROJECT p on ep.PROJ_ID = p.PROJ_ID
> GROUP BY 1, 2, 3
>
> (I use the above quite frequently)
>
> WITH EMP_COUNT(PROJ_ID, EMPLOYEES)
> AS (SELECT PROJ_ID, COUNT(*)
> FROM EMPLOYEE_PROJECT
> GROUP BY PROJ_ID)
>
> SELECT ec.PROJ_ID, ec.EMPLOYEES, p.PROJ_NAME, p.PRODUCT
> FROM EMP_COUNT ec
> JOIN PROJECT p on ec.PROJ_ID = p.PROJ_ID
>
> (haven't used it for this purpose yet, but WITH can be very useful to simplify complex statements or even solve problems that aren't solvable through ordinary use of GROUP BY - don't quite remember the situation, but with subselects I'm pretty certain I've experienced GROUP BY not being able to produce the result I wanted).
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Gabor Boros
> Sent: 28. april 2009 10:58
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How to eliminate long GROUP BY?
>
> Hi,
>
> I always have problem with GROUP BY when work on a complex query because
> every selected field must be listed in GROUP BY. And I hate this thing.
> I found this: http://www.firebirdfaq.org/faq304
> Is this an official and safe solution?
>
> Gabor
>