sql-server - 在 SQL Server 查询中获取每月数据

Question

我正在尝试按月获取数据，因为我已经创建了查询，但在我的结果中有一些重复的记录，如下所示，使用 SQL Server。

SELECT DM.deptdesc, 
   PIM.itemcode, 
   PIM.itemdesc, 
   Isnull(Sum(IM.issuedqty), 0) AS 'Issued', 
   Datename(month, IM.dt)       AS MONTH, 
   Year(IM.dt)                  AS year 
FROM  poitemmaster AS PIM 
   inner JOIN issuematerial AS IM 
                ON PIM.itemcode = IM.itemcode 
   LEFT OUTER JOIN departmentmaster AS DM 
                ON PIM.deptcode = DM.deptcode 
WHERE  PIM.itemcode = 'ICMEL00001' 
GROUP  BY DM.deptdesc, 
      PIM.itemcode, 
      PIM.itemdesc, 
      IM.issuedqty, 
      Datename(month, IM.dt), 
      Year(IM.dt) 

我的查询返回此数据

但我想喜欢这个

我想要按月计算的数量总和。我应该为此做些什么？

标签： sql-serversql-server-2014

Answer 1

从group by中删除issuedqty列，并在列列表的同一列中添加一个SUM()

SELECT DM.deptdesc, 
   PIM.itemcode, 
   PIM.itemdesc, 
   Sum(ISNULL(IM.issuedqty, 0) AS 'Issued', 
   Datename(month, IM.dt)       AS MONTH, 
   Year(IM.dt)                  AS year 
FROM  poitemmaster AS PIM 
   inner JOIN issuematerial AS IM 
                ON PIM.itemcode = IM.itemcode 
   LEFT OUTER JOIN departmentmaster AS DM 
                ON PIM.deptcode = DM.deptcode 
WHERE  PIM.itemcode = 'ICMEL00001' 
GROUP  BY DM.deptdesc, 
      PIM.itemcode, 
      PIM.itemdesc, 
      Datename(month, IM.dt), 
      Year(IM.dt)