1

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
user7401478
  • 1,372
  • 1
  • 8
  • 25
  • 1
    You need to repeat the sum expression in the `HAVING` – HoneyBadger Jan 25 '21 at 10:43
  • @HoneyBadger Will this re-select the sum for every row to find out if it needs to be included? – user7401478 Jan 25 '21 at 10:45
  • @user7401478 most probably not. And if it would - it would do so regardless of sing alias or not. However why asking? you can just easily check it! Make a `selectable stored procedure` and let it count how many time it was called (easiest thing to do - just pulling from some `generator` in SP and then returning zero). – Arioch 'The Jan 25 '21 at 12:12
  • Related (for SQL Server, but the explanation applies): [Why can't I use an alias for an aggregate in a having clause?](https://stackoverflow.com/questions/14048672/why-cant-i-use-an-alias-for-an-aggregate-in-a-having-clause) – Mark Rotteveel Jan 25 '21 at 12:28

1 Answers1

2

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
Popeye
  • 35,427
  • 4
  • 10
  • 31