How to use GROUP BY in firebird

Question

Structure T_TABLE2 -

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 the SQL editor in firebird to check my query. Request -

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

but I got this error in a group.

 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 come from MySQL. MySQL - IMHO is misleading, wrong, and in a black-magic, unpredictable way - allows you to specify partial GROUP BY queries, and the database engine tries to figure out from the rest of the query what value of the ungrouped columns you want. On the other hand, standard SQL (Firebird and most other RDBMS) does not work; it requires that any non-aggregate columns be contained in a group and any columns without a group to explicitly indicate 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 does not know or does not guarantee this), or if you want to group one or more columns or, perhaps, you forgot to fill (you want Are you SUM ?)

---

Examples of valid queries:

• 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 

• Group also MEASUREMENT and return MIN LL and MAX UL:

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

• SUM non-group columns:

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

• Aggregate combination:

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

Answer 2

You must apply some aggregate function ( COUNT() , MIN() , MAX() , SUM() , ...) to each column in SELECT 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 use is to get a list of different values

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

which is equivalent

 SELECT DISTINCT TEST_DESC, MEASUREMENT, LL, UL FROM T_TABLE2 

If you were more specific in your question about what exactly you are trying to achieve with this request, then the answer could satisfy your specific needs.

Answer 3

Although some databases, such as MySQL, are more lenient, in standard SQL, when using GROUP BY the SELECT list should contain only columns, grouped and aggregated functions (for example, SUM() , MAX() ). If you are allowed to specify other columns, it is unpredictable from which rows of the column these columns are grouped, you can get a combination 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