Darren,

SQL Server doesn't accept column aliases in WHERE, HAVING or GROUP BY clause. You either have to use column expression again or use derived table as shown bellow.

SELECT * 
	FROM (select customer_id,customer_nme, 
		(select sum(sell_amount) from sales 
		where sales.customer_id=customer.customer_id) as sales_total
		from customer) dt
where sales_total>0
order by 3,2 desc

Using derived table most likely will produce the same execution plan as using expression and shouldn't affect query performance.


>In this example:
>
>select customer_id,customer_nme,(select sum(sell_amount) from sales where sales.customer_id=customer.customer_id) as sales_total
>from customer
>where sales_total>0
>order by 3,2 desc
>
>Why do I get the error message: Invalid column name 'sales_total'?
>Is there a way around this without having to repeat the query in the where command (similar to the order command where you just specify the column number)?