My query -
select cu.CustomerID,cu.FirstName,cu.LastName, COUNT(si.InvoiceID)as inv --1
from Customer as cu inner join SalesInvoice as si --2
on cu.CustomerID = si.CustomerID -- 3
-- put the WHERE clause here ! --4
group by cu.CustomerID,cu.FirstName,cu.LastName -- 5
where cu.FirstName = 'mark' -- 6
Output with correct code -
Error i get - Incorrect syntax near the keyword 'where'.
Can you tell me why I get this error ? I want to know why WHERE comes before GROUP BY and not after.
You have the order wrong. The WHERE clause goes before the GROUP BY:
select cu.CustomerID,cu.FirstName,cu.LastName, COUNT(si.InvoiceID)as inv
from Customer as cu
inner join SalesInvoice as si
on cu.CustomerID = si.CustomerID
where cu.FirstName = 'mark'
group by cu.CustomerID,cu.FirstName,cu.LastName
If you want to perform a filter after the GROUP BY, then you will use a HAVING clause:
select cu.CustomerID,cu.FirstName,cu.LastName, COUNT(si.InvoiceID)as inv
from Customer as cu
inner join SalesInvoice as si
on cu.CustomerID = si.CustomerID
group by cu.CustomerID,cu.FirstName,cu.LastName
having cu.FirstName = 'mark'
A HAVING clause is typically used for aggregate function filtering, so it makes sense that this would be applied after the GROUP BY
To learn about the order of operations here is article explaining the order. From the article the order of operation in SQL is:
To start out, I thought it would be good to look up the order in which SQL directives get executed as this will change the way I can optimize:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
Using this order you will apply the filter in the WHERE prior to a GROUP BY. The WHERE is used to limit the number of records.
Think of it this way, if you were applying the WHERE after then you would return more records then you would want to group on. Applying it first, reduces the recordset then applies the grouping.
The where clause comes before the group by because conceptually you filter before you group, not after. You want to restrict the output of the that is grouped to only those that match rather than perform the grouping on items that you will, potentially, throw away due to the filter.
SQL does allow you to filter on the results of a GROUP BY -- it's called the HAVING clause.
If you want to filter on something that could be determined prior to the grouping (i.e. everyone with FirstName = 'Mark'), that's done via WHERE.
However, if you want to filter on everyone with 4 or more invoices (i.e., something you wouldn't know until after doing the COUNT), then you use HAVING.
The WHERE clause is used before GROUP BY, because it makes more sense. The filter specified in the WHERE clause is used before grouping. After grouping, you can have a HAVING clause, which is similar to WHERE, except you can filter by aggregate values as well.
Compare:
-- Select the number of invoices per customer (for Customer 1 only)
SELECT
si.CustomerID,
COUNT(*) as InvoiceCount
FROM
SalesInvoice as si
WHERE
si.CustomerID = 1
-- You cannot filter by count(*) here, because grouping hasn't taken place yet.
GROUP BY
si.CustomerID -- (Not needed in this case, because of only 1 customer)
against
-- Select all invoices of customers that have more than three invoices
SELECT
si.CustomerID,
COUNT(*) as InvoiceCount
FROM
SalesInvoice as si
GROUP BY
si.CustomerId
HAVING
-- You can filter by aggregates, like count, here.
COUNT(*) > 3
Let's say you have 100,000 people in your database. 9 of whom are named Mark. Why should the database do a Count operation on all 100,000, then throw out the 99,991 NOT named Mark? Doesn't it seem smarter to filter out the Marks first, then do the Count only 9 times? Makes the operation a whole lot faster.