How to get Month wise summary in MS SQL

One of the common query I used to see in Microsoft forum is “How to get month and year from a datetime column in SQL Server”, Since I saw this question today again I thought I will write small article about this.
Mostly we require this query when we generate reports. Month wise report or Month and Year wise report definitely require such kind of query.
We will directly jump on to the solution and the query.
First we will create a table with datetime field and two numeric columns to get the sum based on the month and year.

CREATE TABLE [dbo].[tblBill](

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

Now we will insert some data,

INSERT INTO [dbo].[tblBill] ([BillDate] ,[Expense],[travel]) VALUES ('2014-05-19',250 ,100)

INSERT INTO [dbo].[tblBill] ([BillDate] ,[Expense],[travel]) VALUES ('2014-05-10',350 ,150)

INSERT INTO [dbo].[tblBill] ([BillDate] ,[Expense],[travel]) VALUES ('2014-04-08',200 ,50)

INSERT INTO [dbo].[tblBill] ([BillDate] ,[Expense],[travel]) VALUES ('2014-04-29',150 ,200)

INSERT INTO [dbo].[tblBill] ([BillDate] ,[Expense],[travel]) VALUES ('2014-03-01',50 ,300)

INSERT INTO [dbo].[tblBill] ([BillDate] ,[Expense],[travel]) VALUES ('2014-03-25',150 ,200)

GO

We will fetch complete data from the table and see how does it looks like,

Now it is the time to write the query to get the data month and year wise.

SELECT    CONVERT(VARCHAR(7),BillDate,126), 

        SUM (CONVERT(int,expense)) as [Sum Expense], 

        SUM (CONVERT(int,travel)) as [Sum Travel]

 FROM tblbill 

 group by CONVERT(VARCHAR(7),BillDate,126)

Just look at the output what we get from above query,

In the above query you can notice that below query,

 select CONVERT(VARCHAR(7),GETDATE(),126)

will give you the year and month from the datetime field.
I hope you enjoyed reading this simple article.