MySQL Monthly Report

Here is is brief howto on generating a Monthly MySQL report from an orders table.

This example assumes you have a table called orders a stored date field called added and a price field for each order - in this example the price is stored as a varchar so it is casted using a (0+price).

The fist column returned is the current month, the second is the total price for that month. Here is the MySQL SQL Monthly Report Query;


SELECT TIMESTAMPDIFF(MONTH, STR_TO_DATE(concat(month(added),'/1/',year(added)), '%m/%d/%Y'), STR_TO_DATE(concat(month(now()),'/',DAYOFMONTH(LAST_DAY(now())),'/',year(now())), '%m/%d/%Y')) as tmonth,
sum(0+total_price) as lprice
from orders
where valid=1 and total_price > 0
group by tmonth


There probably are quicker ways of doing this but it seems like the best solution for reporting on a single orders table. This example basically makes the date as a string using CONCAT and then compare it.

I hope this helps.