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