Compare Months list with date field of table

Question

I am using sql server 2008 I have table in my database is like this:

And I want output like this:

As it is shown in my table I have DateField which has smalldatetime datatype and along with fruits and vegi fields. I want output like which shows data month-wise.. month comparison should be performed based on DateField of my table.

Answer 1

You can use something like:

select [Month] = month(DateField)
  , [MonthName] = left(datename(mm, DateField), 3)
  , TotalAmountApple = sum(case when fruits = 'Apple' then 1 else 0 end)
  , TotalAmountOnion = sum(case when vegi = 'Onion' then 1 else 0 end)
from produce
group by month(DateField)
  , left(datename(mm, DateField), 3)
order by [Month]

Full test details (no SQL Fiddle as it's experiencing issues):

create table produce
(
  id int
  , fruits varchar(10)
  , vegi varchar(10)
  , DateField smalldatetime
)

insert into produce
select 1, 'Apple', 'Chilly', '01-jan-2013'
insert into produce
select 1, 'Mango', 'Onion', '15-jan-2013'
insert into produce
select 1, 'Mango', 'Chilly', '20-jan-2013'
insert into produce
select 1, 'Apple', 'Chilly', '01-Feb-2013'
insert into produce
select 1, 'Mango', 'Onion', '15-Feb-2013'
insert into produce
select 1, 'Apple', 'Onion', '20-Feb-2013'

select [Month] = month(DateField)
  , [MonthName] = left(datename(mm, DateField), 3)
  , TotalAmountApple = sum(case when fruits = 'Apple' then 1 else 0 end)
  , TotalAmountOnion = sum(case when vegi = 'Onion' then 1 else 0 end)
from produce
group by month(DateField)
  , left(datename(mm, DateField), 3)
order by [Month]