SQL grouping by month and year

Question

I'm not sure what should I write in the following SQL query to show 'date' column like this: "month-year" - "9-2011".

SELECT MONTH(date) + '.' + YEAR(date) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada  FROM [Order] WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY MONTH(date), YEAR(date) 

So, what I want to do is to change the data from the first column to show month and year instead of showing month only.

Answer 1

SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada  FROM [Order] WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) 

Or as @40-Love mentioned you can cast with leading zeroes:

GROUP BY    CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2)  

Answer 2

I'm guessing this is MS SQL, as it looks like MS SQL syntax.

You should put the same thing in the group by as you have in the select.

For example:

Select MONTH(date)+'-'+YEAR(date), .... ... ... ... group by MONTH(date)+'-'+YEAR(date)