按年购买SQL总量 [英] SQL total quanity purchased by year

问题描述

在AdventureWorks2012数据库中，我必须使用Purchasing.PurchaseOrderDetail表编写查询，并列出购买的总量对于2006年的每个产品和标签总额为TotatQtyPurchased。

这是我最新的查询

  SELECT POD.ProductID，POD.ModifiedDate，
 SUM（OrderQty）AS TotalQtyPurchased 
 FROM Purchasing.PurchaseOrderDetail POD 
 GROUP BY POD.ProductID 
 HAVING ModifiedDate ='2006'
  

但是我得到这个错误。

'Purchasing.PurchaseOrderDetail.ModifiedDate'在HAVING子句中无效，因为它不包含在聚合函数或GROUP BY子句中。

这非常令人沮丧。 / p>

更新：当我试图为我的查询指定日期2006时，每当我输入以下命令时，我总是得到一个空白执行（即没有行或列） ：

WHERE ModifiedDate LIKE'2006％'
或
WHERE ModifiedDate ='2006'

解决方案

你很近，大多数SQL错误消息te你会发现错误是什么，需要一些练习来解释它们。

• 现在取出HAVING子句...




• 对WHERE子句进行一些研究，弄清楚如何使用WHERE
  
  来代替HAVING。因为这是功课，我不想给你答案，但这应该得到正确的方向
  

  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'

  解决方案

  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

  这篇关于按年购买SQL总量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！