SQLTeam.com | Weblogs | Forums

Group by


#1

Hi,
Please see the input and expected output to get the total items which are monthly sold.

ItemCode  Date        Quantity    IsSold
X         1-sep-2015     1          1

X         2-sep-2015     2         1

X         3-sep-2015      3        1

X         3-sep-2015      1        0

X         20-aug-2015     4        1

Output

Item:X

Month           Sold

Sep 2015         6
Aug  2015       4

Thanks.


#2
select  dateadd(month, datediff(month, 0, [Date]), 0), sum(Quantity)
from    yourtable
where   IsSold  = 1
group by dateadd(month, datediff(month, 0, [Date]), 0)

#3

Thanks. It helped.
Is it possible to show the not sold itemcount along with it?

Month           Sold   NotSold

Sep 2015         6        1
Aug  2015       4         0

#4

Option 1:

select dateadd(month,datediff(month,0,[Date]),0) as [Month]
      ,sum(Quantity*sign(IsSold)) as Sold
      ,sum(Quantity*abs(sign(IsSold)-1)) as NotSold
  from yourtable
 group by dateadd(month,datediff(month,0,[Date]),0)
;

Option 2:

select dateadd(month,datediff(month,0,[Date]),0) as [Month]
      ,sum(case when IsSold=0 then 0 else Quantity end) as Sold
      ,sum(case when IsSold=0 then Quantity else 0 end) as NotSold
  from yourtable
 group by dateadd(month,datediff(month,0,[Date]),0)
;