dhilwala2001
asked on
sql Select Question
How come
Select Avg(InvoiceAmount) as TtlAvgInvoices, CustomerId from Invoices Group by CustomerId Having Avg(InvoiceAmount) > 50.00
works
BUT
Select Avg(InvoiceAmount) as TtlAvgInvoices, CustomerId from Invoices Group by CustomerId Having Avg(InvoiceAmount) > 50.00
Where InvoiceAmount > 10.00
doesn't work, stating
"Incorrect Syntax near 'Where' "
I was under the impression that Where was the first thing that would execute and restrict the records pulled, before the group by and having clause get executed.
Why does the above statement yield this unexpected error.
(To try it, simply create an Invoices table with the fields mentioned in the query, enter some data, and try out the pull condition that is erroring out)....
Select Avg(InvoiceAmount) as TtlAvgInvoices, CustomerId from Invoices Group by CustomerId Having Avg(InvoiceAmount) > 50.00
works
BUT
Select Avg(InvoiceAmount) as TtlAvgInvoices, CustomerId from Invoices Group by CustomerId Having Avg(InvoiceAmount) > 50.00
Where InvoiceAmount > 10.00
doesn't work, stating
"Incorrect Syntax near 'Where' "
I was under the impression that Where was the first thing that would execute and restrict the records pulled, before the group by and having clause get executed.
Why does the above statement yield this unexpected error.
(To try it, simply create an Invoices table with the fields mentioned in the query, enter some data, and try out the pull condition that is erroring out)....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
Also, the difference between WHERE and HAVING is that WHERE tests the data in non-GROUP BY (Sum, Count, First, Min, Max, etc) fields, whereas HAVING tests the data in GROUP BY Fields. (SELECT Sum(sales) as total_sales, day FROM MyTable GROUP BY day WHERE total_sales > 100000)