Group by month with rollup when date is yyyy-mm-dd

Question

I'm doing an online course in SQL and I need to have date in yyyy-mm-dd format. I need to group by month to show total sales of medalId and total medalId sold in a month.

I just can't seem to be able to engineer a way to rollup with month using this format. Any advice and guidance would be really appreciated:

SELECT date, COUNT(medalId) AS totalProducts, SUM(medalId) as totalSales
from orders
GROUP BY date WITH ROLLUP;

Answer 1

MySQL has the DATE datatype for columns. Use it. Take the trouble to avoid using VARCHAR() or CHAR() columns to hold dates. It's worth it because you get a lot of cool date arithmetic when you do that. Also, they sort correctly. Your 2020-03-25 format is compatible with the DATE datatype.

You want something like this, using the LAST_DAY() date-arithmetic function.

SELECT LAST_DAY(date) month_ending, 
       COUNT(medalId) AS totalProducts, SUM(medalId) as totalSales
from orders
GROUP BY LAST_DAY(date) WITH ROLLUP;

Pro tip: When doing database programming, it is worth your time to learn your DBMS's date/time and string processing functions.