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

User · Answer

In postgresql I can write a similar query with a date-format function (to_char) and grouping just by date:

SELECT to_char (datum, 'MM-YYYY') AS mjesec 
FROM test 
GROUP BY datum 
ORDER BY datum;

Such thing is surely possible with SQL-Server too, isn't it?

User · Answer

For mariaDB you can   SELECT DATE FORMAT date    m- Y   FROM  Order  GROUP BY  DATE FORMAT date    m- Y     Link  https   mariadb com kb en library date format

User · Answer

If you want to stay having the field in datetime datatype  try using this   SELECT DATEADD MONTH  DATEDIFF MONTH  0  o  date    0  AS Mjesec  SUM marketingExpense  AS SumaMarketing  SUM revenue  AS SumaZarada  FROM  Order  o WHERE  idCustomer   1  AND  o  date  BETWEEN  2001-11-3  AND  2011-11-3   GROUP BY DATEADD MONTH  DATEDIFF MONTH  0  o  date    0    It it also easy to change to group by hours  days  weeks  years    I hope it is of use to someone   Regards

User · Answer

If I understand correctly  In order to group your results as requested  your Group By clause needs to have the same expression as your select statement   GROUP BY MONTH date          YEAR date    To display the date as  month-date  format change the     to  -  The full syntax would be something like this   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

User · Answer

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

User · Answer

I guess is MS SQL as it looks like MS SQL syntax    So you should put in the group line the same thing as in select ex   Select MONTH date   -  YEAR date                    group by MONTH date   -  YEAR date

User · Answer

Yet another alternative    Select FORMAT date  MM yy           group by FORMAT date  MM yy

User · Answer

SQL Server 2012 above  I prefer use format   function  more simplify   SELECT format date  MM yyyy   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 format date  MM yyyy

User · Answer

You can try multiplication to adjust the year and month so they will be one number  This  from my tests  runs much faster than format date  yyyy MM    I prefer having the year before month for sorting purpose  Code created from MS SQL Server Express Version 12 0   SELECT  YEAR Date    100    MONTH Date  AS yyyyMM FROM  Order      GROUP BY  YEAR Date    100    MONTH Date  ORDER BY yyyyMM

[sql] SQL grouping by month and year

Examples related to sql

Examples related to sql-server