Sum monthly sales totals and provide output with month name and total for last 3 months-CodePudding

I am trying to get monthly sales totals from a MYSQL database and have them summed by month with month name for last X months. The two columns I am targeting are: total_customer_charged and local_time. I almost have my query working correctly, the problem I have is that the order the results come out sometimes changes with each query. Can anyone please give me some hints to get my query correct?

My existing query is:

select date_format(local_time,'%M'),sum(total_customer_charged) FROM ORDERS WHERE local_time BETWEEN curdate() - INTERVAL 3 MONTH AND curdate() group by month(local_time) order by year(local_time),month(local_time)

CodePudding user response：

You should put year into the group-by statement like this:

select
    date_format(local_time, '%Y') AS agg_year,
    date_format(local_time, '%M') AS agg_month,
    sum(total_customer_charged) AS monthly_total_customer_charged
FROM
    ORDERS
WHERE
    local_time BETWEEN curdate() - INTERVAL 3 MONTH AND curdate()
group by
    date_format(local_time, '%Y'),
    date_format(local_time, '%M')
order by
    agg_year,
    agg_month

CodePudding user response：

SELECT MONTHNAME(local_time), SUM(total_customer_charged) 
FROM ORDERS 
GROUP BY YEAR(local_time), MONTH(local_time)