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)....


dhilwala2001Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Standard query structure:

SELECT
FROM
WHERE
GROUP BY
ORDER BY
HAVING

You have to keep this structure in your SQL.  Your second query doesn't work because WHERE is below HAVING.

Hope this helps.
-Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.