@MadMyche has shown you the way forward, I am going to dissect your attempt to try to help you see where you went wrong.
When you get an error message like that, start looking at your sub-queries - you only have one and it's in your WHERE clause
SELECT SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = Invoice.ProductId
INNER JOIN Invoice ON Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY ProductName, MONTH(Date)
Firstly - there is a typing error there - you are trying to join tables
InvoiceLine
and
Product
based on a column in table
Invoice
- that is not going to work (or even compile). That should read
SELECT SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = InvoiceLine.ProductId
INNER JOIN Invoice ON Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY ProductName, MONTH(Date)
If you run
just that bit of the query you will see that it returns as many rows as you have products. You are trying to test many things against a
single value - and that is why you get the error message.
Let's look at that a little deeper - you are GROUPing by month of [date] - but you will only ever have April data because of your WHERE clause - so there is no need to include MONTH([date]) in the GROUP BY
You had already worked out that you needed to use WHERE .. IN but you hadn't identified which Products were IN the criteria that you had set..so try changing the query to
SELECT Product.ProductID, SUM(InvoicedQuantity) AS TotalAmount FROM InvoiceLine INNER JOIN Product ON Product.ProductID = InvoiceLine.ProductId
INNER JOIN Invoice ON #Invoice.InvoiceID = InvoiceLine.InvoiceID WHERE MONTH(Date) = 4 GROUP BY Product.ProductID
Note I've had to tell SQL
which table contains the ProductID to group by to avoid the error "Ambiguous column name 'ProductID'." Now when I run that query, I'm still getting one row per product, but now I know which total goes with which product.
To be honest, the only time we care about that total is when it's greater than 15 - so let's put that restriction in now. Note we can't say
where Month([date]) = 4 AND SUM(InvoiceQuantity) > 15
If you try you will get an error message "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.". That's why @MadMyche's query has
HAVING Sum(InvoiceQuantity) > 15
Now when you run that sub-query you will only get ProductIDs returned that fit your criteria. That collection is the only thing you need and that is what you use for your IN clause - see Solution 1 for the full query.
Looking at your original query it looks as if you were trying to use another construct - where you can update a table using joins. If you wanted to increase the quantity for every product for which there was a sale in April for example you could use
UPDATE p SET Quantity = Quantity + 10
from #Invoice i
inner join #InvoiceLine il on i.InvoiceID = il.InvoiceID
inner join #Product p on il.ProductID = p.ProductID
WHERE MONTH(i.[date]) = 4
Note the use of the ALIAS "p". I'm just throwing this example in so you can use it in the future - it won't work for this example because you need to GROUP BY to get total sales.