按计数分组和总计数 [英] Group By Count and Total Count

问题描述

我试图弄清楚如何通过查询获得以下结果，但无法执行相同操作.

I am trying to figure out how can I get following result by query but unable to do the same.

表名称:ProductMaster

Table Name: ProductMaster

ProductCode   Quantity   EntryDate
A1            10         10/03/2015
A1            10         15/03/2015
A2            10         18/03/2015
A2            10         25/03/2015
A1            10         10/04/2015
A2            10         15/04/2015

我想得到结果 如果我选择3月，则结果应为:

I want to get result as If I select March month, result should be as:

ProductCode   MonthCount   TotalCount
A1            20           30         
A2            20           30

如果我选择4月，则结果应为:

If I select April month, result should be as:

ProductCode   MonthCount   TotalCount
A1            10           30         
A2            10           30

我的查询:

SELECT ProductCode, SUM(Quantity) 
FROM ProductMaster
WHERE DATEPART(MONTH, EntryDate) = @Month
GROUP BY ProductCode

@month = 3或4，根据输入.

此外，我如何获得产品代码的数量. 对于Month = 3

Additionally, How can i get the count of productcode. For Month = 3

ProductCode   MonthCount   TotalCount
A1            2            3         
A2            2            3

推荐答案

您可以使用基于@Month的case表达式:

You could use a case expression based on @Month:

SELECT   ProductCode, 
         SUM (CASE WHEN MONTH(EntryDate) = @Month THEN Quantity ELSE 0 END) 
           AS MonthCount,
         SUM (Quantity) AS TotalConount
FROM     ProductMaster
GROUP BY ProductCode

要回答已编辑的问题，可以对count而不是sum使用相同的技术:

To answer the edited question, you can use the same technique with count instead of sum:

SELECT   ProductCode, 
         COUNT (CASE WHEN MONTH(EntryDate) = @Month THEN Quantity ELSE NULL END) 
           AS MonthCount,
         COUNT (*) AS TotalConount
FROM     ProductMaster
GROUP BY ProductCode

这篇关于按计数分组和总计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！