Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Distinct and Group By in the same sql doesn’t make sense. Since you want the Sum() aggregation by year and month – use Group By and lose Distinct…
    You should adjust your code like here:

    WITH     --  S a m p l e     D a t a :
  supermarket_sales (DATE_STR, VAL) AS
    ( Select '01/17/2019', 548.971 Union All 
      Select '03/15/2019', 80.22 Union All 
      Select '03/19/2019', 340.526 Union All 
      Select '01/24/2019', 489.048 Union All 
      Select '02/21/2019', 634.378 Union All 
      Select '03/07/2019', 627.617 Union All 
      Select '02/21/2019', 433.692 Union All 
      Select '02/21/2019', 772.38 Union All 
      Select '01/26/2019', 76.146 Union All 
      Select '02/27/2019', 172.746 Union All 
      Select '02/28/2019', 60.816 
    ) 

    --     S Q L :
SELECT   MonthName( Str_To_Date( DATE_STR, '%m/%d/%Y' ) ) AS month, 
         Year( Str_To_Date( DATE_STR, '%m/%d/%Y' ) ) AS year,
         Sum(VAL) as total
FROM     supermarket_sales
GROUP BY year, month

    /*
month       year         total
----------  ----   -----------
January     2019      1114.165
March       2019      1048.363
February    2019      2074.012   */

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search