Search code examples
sqlmysql

get sum of sales per month

Trying to write a query to get the total number of sales per month.

So I can currently write this query to return the month and year:

SELECT 
  DISTINCT(MONTHNAME(STR_TO_DATE(`date`, '%m/%d/%Y'))) AS 'Month'
  ,YEAR(STR_TO_DATE(`date`, '%m/%d/%Y')) AS 'Year'
  ,`total` 
FROM `supermarket_sales`

And I can return the below:

enter image description here

So what I want to do now is get the total for each month.

Using an answer found here: Total sales per month

And this is my attempt to mimic the accepted answer on the link above:

SELECT 
  DISTINCT(MONTHNAME(STR_TO_DATE(`date`, '%m/%d/%Y'))) AS 'Month',
  YEAR(STR_TO_DATE(`date`, '%m/%d/%Y')) AS 'Year',
  SUM(`total`) AS 'TotalSales'
FROM `supermarket_sales`
GROUP BY YEAR(STR_TO_DATE(`date`, '%m/%d/%Y')), MONTH(STR_TO_DATE(`date`, '%m/%d/%Y'))
ORDER BY YEAR(STR_TO_DATE(`date`, '%m/%d/%Y')), MONTH(STR_TO_DATE(`date`, '%m/%d/%Y'))

But I am getting the below error:

enter image description here

How can I make this work?

Solution

  • Once you use aliases for column in SELECT statement you can refer to same later in the query.

    i have tried to create simple table with default date format. It is working fine for me.

    creating table:

    create table supermarket_sales(date date, total float);

    inserting values:

    insert into supermarket_sales values
('2019-01-01', 548.971),
('2019-03-01', 80.22),
('2019-03-01', 340.526),
('2019-01-01', 489.048),
('2019-02-01', 634.378),
('2019-03-01', 627.617),
('2019-02-01', 433.692),
('2019-02-01', 772.38),
('2019-01-01', 76.146),
('2019-02-01', 172.746),
('2019-02-01', 60.816);

    selecting records as you showed in first query.

    select monthname(date) as month, year(date), total as year 
from supermarket_sales;

    output of query1

    Adding group by and order by:

    select distinct(monthname(date)) as month, year(date) as year, sum(total) as year 
from supermarket_sales
group by year,month;

    output of query2