Menu
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.
677
You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.
Group By Month Name In the above SQL query we use date_format(order_date, “%M”) to convert a date column into month name, and use sum column to add up the sales amounts.
You simply use the aggregate function (here: SUM ) with the correct column and at the end of the query you group by year . You can rename the column using the AS keyword with a new name. In this solution, you don't use a GROUP BY clause.
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) 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
