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.
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]