Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - 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).
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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
    

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...