Firebird column unknown on SELECT AS

I have a table of calls and their durations:

+----+---------------+---------------+---------------+
| ID | CALLER_NUMBER | CALLEE_NUMBER | CALL_DURATION |
+----+---------------+---------------+---------------+
|  0 | 1xxx0001020   | 1xxx3024414   |          0.30 |
|  1 | 1xxx1002030   | 1xxx0303240   |        134.24 |
|  2 | 1xxx2003040   | 1xxx0324220   |        330.00 |
|  3 | 1xxx3004050   | 1xxx5829420   |        104.00 |
+----+---------------+---------------+---------------+

I want to select all numbers that called for over 500 seconds in total. My query uses SELECT AS to rename the SUM(...) field so I can reference it in the HAVING clause:

SELECT CALLER_NUMBER, SUM(CALL_DURATION) AS TOTAL
FROM CALLS
GROUP BY CALLER_NUMBER
HAVING TOTAL > 500

When I run this query in Firebird SQL, I get the following error:

Message: isc_dsql_prepare failed

SQL Message : -206
can't format message 13:794 -- message file not found

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
TOTAL
At line 4, column 8

Answer

You can not use the alias in the having clause.

You should use sum in the having clause as follows:

SELECT CALLER_NUMBER, SUM(CALL_DURATION) AS TOTAL
FROM CALLS
GROUP BY CALLER_NUMBER
HAVING SUM(CALL_DURATION) > 500

Advertisement

Answer