How to use GROUP BY in firebird

Question

The structure of T_TABLE2 is

ID INT
TBL1_ID INT
TESTER VARCHAR
LOT_ID VARCHAR
GRP VARCHAR
SITE_NUM INT
TEST_NUM VARCHAR
TEST_DESC VARCHAR
MEASUREMENT DOUBLE PRECISION
UNIT VARCHAR
LL DOUBLE PRECISION
UL DOUBLE PRECISION
STATUS VARCHAR

and I use SQL editor in Firebird to test my query. The query is

SELECT TEST_DESC, MEASUREMENT, LL, UL 
FROM T_TABLE2 
GROUP BY TEST_DESC

but I got this error in GROUP BY.

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Answer 1

You must be coming from MySQL. MySQL - IMHO misleadingly, incorrectly, and in a black-magicky, unpredictible sort of way - allows you to specify partial GROUP BY queries and the database engine tries to figure out from the rest of the query which value of the non-grouped-by columns you want. Standard SQL (Firebird and most other RDBMSes), on the other hand, does not; it requires any non-aggregate columns to be contained in the group by, and any non-group-by columns to explicitly specify which row you want.

In your case, the offending columns are MEASUREMENT, LL, and UL. You need to specify which MEASUREMENT, LL, and UL you want (yes, even if they are all the same; the database engine has no way of knowing or guaranteeing this), or if you want to group by one or more of the columns or possibly you forgot to aggregate (Did you want the SUM?)

---

Examples of valid queries:

1. Group by all columns (equivalent to a SELECT DISTINCT):

   SELECT TEST_DESC, MEASUREMENT, LL, UL
   FROM T_TABLE2
   GROUP BY TEST_DESC, MEASUREMENT, LL, UL
   

2. Group by MEASUREMENT as well and return the MIN LL and MAX UL:

   SELECT TEST_DESC, MEASUREMENT, MIN(LL), MAX(UL)
   FROM T_TABLE2
   GROUP BY TEST_DESC, MEASUREMENT
   

3. SUM non-grouped columns:

   SELECT TEST_DESC, SUM(MEASUREMENT), SUM(LL), SUM(UL)
   FROM T_TABLE2
   GROUP BY TEST_DESC
   

4. A combination of aggregates:

   SELECT TEST_DESC, COUNT(DISTINCT MEASUREMENT), SUM(LL), MAX(UL)
   FROM T_TABLE2
   GROUP BY TEST_DESC