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