Filtering and Aggregate functions

[Jason]

I have a complex statement that is used in a SELECT statement. After
all my calculations I need to do an additional filter on the results.
Can I use an alias anywhere or is the AS clause just for column
display purposes?

Instead of:

SELECT column1,
column2,
some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statements
AS Total
FROM table_name
HAVING
some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statements

1

Can I have:

SELECT column1,
column2,
some_complex_statement_using_SUM_AVG_and_a_few_CAS E_statements
AS Total
FROM table_name
HAVING
Total > 1

As I understand it, the HAVING clause is used for filtering AFTER
aggregate functions are calculated so the alias SHOULD be available.

[David Portas]

You can only reference aliases in the ORDER BY clause of a SELECT statement.
Use a derived table instead. Here's an example from Pubs:

SELECT *
FROM
(SELECT type, SUM(advance) AS total_advance
FROM titles
GROUP BY type) AS T
WHERE total_advance>=20000

The outer WHERE clause is equivalent to a HAVING clause on the inner query.

--
David Portas
------------
Please reply only to the newsgroup
--