Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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


0
dhilwala2001
Asked:
dhilwala2001
  • 2
1 Solution
 
Jim HornMicrosoft 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now