sql - Group By Count and Total Count -
i trying figure out how can following result query unable same.
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 i want result if select march month, result should as:
productcode monthcount totalcount a1 20 30 a2 20 30 if select april month, result should as:
productcode monthcount totalcount a1 10 30 a2 10 30 my query:
select productcode, sum(quantity) productmaster datepart(month, entrydate) = @month group productcode where @month = 3 or 4, based on input.
additionally, how can count of productcode. month = 3
productcode monthcount totalcount a1 2 3 a2 2 3
you use case expression based on @month:
select productcode, sum (case when month(entrydate) = @month quantity else 0 end) monthcount, sum (quantity) totalconount productmaster group productcode edit:
answer edited question, can use same technique count instead of sum:
select productcode, count (case when month(entrydate) = @month quantity else null end) monthcount, count (*) totalconount productmaster group productcode
Comments
Post a Comment