How to resolve SQL SUM not working correctly?-CodePudding

I have an SQL data table called sales that records product, quantity sold, unit price, and the date of the sale. I would like to create a view with monthly sales for chairs from 2000-2010. That is, for every unique month and year pair I would like to sum up quantity * unit_price where the product = "CHAIR". My attempt is the following:

CREATE VIEW MONTHLY_SALES(product, monthly_sales, month, year) AS (
SELECT product, SUM(quantity * unit_price) AS monthly_sales,
           TO_CHAR(sale_date, 'Month') AS month, EXTRACT(YEAR FROM sale_date) AS year
FROM sales
WHERE sale_date BETWEEN '2000-01-01' AND '2010-12-31' AND product = 'CHAIR'
GROUP BY quantity, unit_price, article, sale_date);
    
SELECT *
FROM MONTHLY_SALES;

I would expect to sum over the same month and year and get something like:

product | monthly_sales | month | year
Chair     100             January 2001
Chair     110             February 2001
Chair     120             March    2001
Chair     300             April    2001

But I'm seeing different monthly_sales for the same month-year pairs so I don't know why the SUM isn't working. Any assistance to fix this would be much appreciated.

===============================================================

Sample Data:

product | quantity | unit_price | sales_date
Chair     1             40        2001-01-01
Chair     3             40        2001-01-02
Chair     4             40        2001-02-01
Chair     1             40        2001-02-05

Expected Output:

product | monthly_sales | month   | year
Chair     160             January   2001
Chair     200             February  2001

CodePudding user response：

SELECT product, SUM(quantity * unit_price) AS monthly_sales,
           EXTRACT(MONTH from sales_date) AS month, EXTRACT(YEAR FROM sales_date) AS year
FROM sales
WHERE sales_date BETWEEN '2000-01-01' AND '2010-12-31' AND product = 'CHAIR'
group by product, year, month

I've created a fiddle

Your group by is wrong - you're not grouping by product, month and year. What you're asking is "every time you see a change in any of quantity, price, article (whatever that is) and date, perform the sum". That will create lots of duplicates (if you have two sales in a given year/month combination, your group by says "create a new row because you've seen a change in sales date).

CodePudding user response：

You are grouping by the sales date, instead of the month and year of the sale. You will get one record per sales_date instead of one record per month. This example will work in your case:

CREATE VIEW MONTHLY_SALES(product, monthly_sales, month, year) AS (
SELECT product, SUM(quantity * unit_price) AS monthly_sales,
           TO_CHAR(sale_date, 'Month') AS month, EXTRACT(YEAR FROM sale_date) AS year
FROM sales
WHERE sale_date BETWEEN '2000-01-01' AND '2010-12-31' AND product = 'CHAIR'
GROUP BY article, TO_CHAR(sale_date, 'Month'), EXTRACT(YEAR FROM sale_date));
    
SELECT *
FROM MONTHLY_SALES