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

Popular posts from this blog

c++ - No viable overloaded operator for references a map -

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

java - Cannot secure connection using TLS -