How to use GROUP BY in firebird

问题:

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 the test my query. Th 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).

回答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
   

回答2:

While some databases, such as MySQL, are more lenient, in standard SQL when you use GROUP BY, the SELECT list must only contain the columns being grouped by and aggregate functions (e.g. SUM(), MAX()). If you were allowed to specify other columns, it's unpredictable which of the rows of the grouped column these columns will come from -- you may even get a mix of columns from different rows.

So you need to do something like:

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

回答3:

You have to apply some aggregate function (COUNT(),MIN(), MAX(), SUM(),...) to each of the columns in SELECT clause that are not part of GROUP BY.

For example your query might look like

SELECT TEST_DESC, MAX(MEASUREMENT) MAX_MEASUREMENT, MAX(LL) MAX_LL, MAX(UL) MAX_UL
  FROM T_TABLE2 
 GROUP BY TEST_DESC

Another syntactically valid usage is to get a list of distinct values

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

which is equivalent of

SELECT DISTINCT TEST_DESC, MEASUREMENT, LL, UL 
  FROM T_TABLE2 

If you would been more specific in your question about what exactly you're trying to achieve with this query then the answer could've addressed you particular needs.