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

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

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

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

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

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

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

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

[sql] SQL grouping by month and year

Examples related to sql

Examples related to sql-server