| Subject | Aggregate function with CASE and GROUP BY clauses |
|---|---|
| Author | Roberto Freitas |
| Post date | 2005-09-13T10:30:50Z |
I tried the following query against WI-V1.5.2.4731 Firebird 1.5 :
SELECT CASE WHEN (val < 0) then 'C' ELSE 'D' END as CD,
SUM(clc_valor) AS TOT
FROM Lanto
WHERE (cod = '10010001')
GROUP BY 1
but it raises an exception class EIB_ISCError
ISC ERROR CODE:335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Cannot use an aggregate function in a GROUP BY clause
I suppose I could use an aggregate function with CASE and GROUP BY
clauses.
Cant'I or there is something else wrong?
SELECT CASE WHEN (val < 0) then 'C' ELSE 'D' END as CD,
SUM(clc_valor) AS TOT
FROM Lanto
WHERE (cod = '10010001')
GROUP BY 1
but it raises an exception class EIB_ISCError
ISC ERROR CODE:335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Cannot use an aggregate function in a GROUP BY clause
I suppose I could use an aggregate function with CASE and GROUP BY
clauses.
Cant'I or there is something else wrong?