How to use GROUP BY in firebird
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
UL. You need to specify which
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
Examples of valid queries:
Group by all columns (equivalent to a
SELECT TEST_DESC, MEASUREMENT, LL, ULFROM T_TABLE2GROUP BY TEST_DESC, MEASUREMENT, LL, UL
MEASUREMENTas well and return the
SELECT TEST_DESC, MEASUREMENT, MIN(LL), MAX(UL)FROM T_TABLE2GROUP BY TEST_DESC, MEASUREMENT
SELECT TEST_DESC, SUM(MEASUREMENT), SUM(LL), SUM(UL)FROM T_TABLE2GROUP BY TEST_DESC
A combination of aggregates:
SELECT TEST_DESC, COUNT(DISTINCT MEASUREMENT), SUM(LL), MAX(UL)FROM T_TABLE2GROUP BY TEST_DESC
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.
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
You have to apply some aggregate function (
SUM(),...) to each of the columns in
SELECT clause that are not part of
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.