CODEWITHSUNDEEP
sqlsql-servergroup-by
user3047713
user3047713

Reputation: 35

SQL total quanity purchased by year

My last post was closed.

On the AdventureWorks2012 database, I have to write a query using the Purchasing.PurchaseOrderDetail table and list the total quanity purchased for each product during 2006 and label sum as TotatQtyPurchased. I also have to group by ProductID.

Here is my lastest query

SELECT POD.ProductID, POD.ModifiedDate, 
       SUM(OrderQty) AS TotalQtyPurchased 
       FROM Purchasing.PurchaseOrderDetail POD  
       GROUP BY POD.ProductID  
       HAVING ModifiedDate = '2006'

But I get this error.

Column 'Purchasing.PurchaseOrderDetail.ModifiedDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

This is very frustrating.

UPDATE: When I am trying to specify the date "2006" for my query, I always get a blank execution (i.e. no rows or columns) whenever I type the following commands:

WHERE ModifiedDate LIKE '2006%' or WHERE ModifiedDate = '2006'

Upvotes: 0

Views: 129

Answers (3)

Grish
Grish

Reputation: 862

Edited now

Try this

SELECT POD.ProductID, POD.ModifiedDate, SUM(Order_qty)
AS TotalQtyPurchased FROM Purchasing.PurchaseOrderDetail  POD  where ModifiedDate = '2006' GROUP BY 
POD.ProductID

Upvotes: 0

Santhosh
Santhosh

Reputation: 1791

If you are using anything in Having Clause, you need to use aggreagte function. For example, You could use like this 

HAVING MAX(MODIFIEDDATE) = '2006'

But from your problem it looks like, WHERE clause will solve your problem best.If the above query result set is you need, then,

SELECT POD.ProductID, MODIFIEDDATE, SUM(OrderQty) AS TotalQtyPurchased 
       FROM Purchasing.PurchaseOrderDetail POD  
       WHERE ModifiedDate = '2006'
       GROUP BY POD.ProductID, MODIFIEDDATE

I made the edit, what i would suggest is, since you are using Modified date in WHERE clause you don't need to specify that in SELECT clause.

Upvotes: 0

Sparky
Sparky

Reputation: 15095

You are close, and most SQL error messages tell you exactly what the error is, it takes a bit of practice to interpret them.

  • Take out the HAVING clause for now...

  • Do a bit of research on the WHERE clause, figure out how the WHERE
    can be used instead of HAVING...

Since this is homework, I don't want to give you the answer, but this should get in the right direction

Upvotes: 4

Related Questions