Assign sum of column to the month of one date in another column if criteria is met

user_4934150 0
Viewed 48

DB-Fiddle

CREATE TABLE sales (
    id int auto_increment primary key,
    customerID VARCHAR(255),
    sales_date DATE,
    sales_volume INT,
    annual_unqiue_count INT
);

INSERT INTO sales
(customerID, sales_date, sales_volume, annual_unqiue_count)
VALUES 
("Customer_01", "2020-03-01", "600", "1"),
("Customer_01", "2020-03-25", "315", "0"),

("Customer_02", "2020-03-18", "208", "1"),
("Customer_02", "2020-07-25", "140", "0"),

("Customer_03", "2020-10-18", "400", "1"),
("Customer_03", "2020-12-06", "500", "0"),
("Customer_03", "2020-12-18", "438", "0"),
("Customer_03", "2020-12-25", "917", "0");

Expected Result:

customerID      sales_date      SUM(annual_unqiue_count)    SUM(sales_volume)   sales_volume
Customer_01          3                  1                       600               915
Customer_01          3                  0                       315                 0
Customer_02          3                  1                       208               348
Customer_02          7                  0                       140                 0
Customer_03         10                  1                       400              2255
Customer_03         12                  0                       500                 0
Customer_03         12                  0                       438                 0
Customer_03         12                  0                       917                 0

In the result I want to assign the SUM(sales_volume) per customer to the month of the sales_date which has an annual_unqiue_count <> 0. (Note: Only one row per customer can have an annual_unique_account <> 0)

Referring to the solution from this question I tried to go with:

SELECT
customerID,
MONTH(sales_date),
SUM(annual_unqiue_count),
SUM(sales_volume),
  (CASE WHEN annual_unqiue_count <> 0
  THEN SUM(sales_volume) over (PARTITION BY customerId)
  ELSE 0 END) AS sales_volume
FROM sales
GROUP BY 1,2;

However, it does not give me the correct result.
Do you have any idea what I need to change?

mysql sql
4 Answers

There is no reason to use GROUP BY in this case because you want all the rows of the table.
Use SUM() window function to get the last column:

SELECT customerID,
       MONTH(sales_date) sales_date,
       annual_unqiue_count,
       sales_volume,
       annual_unqiue_count *
       SUM(sales_volume) OVER (PARTITION BY customerID) total_sales_volume 
FROM sales

Maybe you need also a WHERE clause to filter the rows for a specific year?

See the demo.
Results:

> customerID  | sales_date | annual_unqiue_count | sales_volume | total_sales_volume
> :---------- | ---------: | ------------------: | -----------: | -----------------:
> Customer_01 |          3 |                   1 |          600 |                915
> Customer_01 |          3 |                   0 |          315 |                  0
> Customer_02 |          3 |                   1 |          208 |                348
> Customer_02 |          7 |                   0 |          140 |                  0
> Customer_03 |         10 |                   1 |          400 |               2255
> Customer_03 |         12 |                   0 |          500 |                  0
> Customer_03 |         12 |                   0 |          438 |                  0
> Customer_03 |         12 |                   0 |          917 |                  0
user_104988280

Try below query

SELECT 
  s.customerID,
  MONTH(s.sales_date),
  s.annual_unqiue_count,
  s.sales_volume, 
  CASE WHEN 
    x.sum_sales_volume IS NULL THEN 0 ELSE x.sum_sales_volume 
  END AS sum_of_sales_volume 
FROM sales s 
LEFT JOIN(
   SELECT SUM(sales_volume) as sum_sales_volume, annual_unqiue_count, id 
   FROM sales GROUP BY customerID
)x ON x.id=s.id;

DB-Fiddle

user_139347660

One way to get the result is using a subquery:

DB-Fiddle

SELECT
t1.customerID,
MONTH(t1.sales_date),
SUM(t1.sales_volume_date)
FROM

  (SELECT
  customerID,
  sales_date,
  SUM(annual_unqiue_count) AS annual_unique_count,
  SUM(sales_volume) AS sales_volume,
    (CASE WHEN annual_unqiue_count <> 0
    THEN SUM(sales_volume) over (PARTITION BY customerId)
    ELSE 0 END) AS sales_volume_date
  FROM sales
  GROUP BY 1,2) t1
  
GROUP BY 1,2;
user_49341500

You can group by the values seperately and then inner join:

    WITH CTE1 AS
    (
    SELECT CUSTOMERID, 
     MONTH(sales_date), 
     annual_unqiue_count, 
      SALES_VOLUME
    FROM SALES
    )

    SELECT A.*, 
    CASE WHEN annual_unqiue_count = 1 THEN X.TOTAL_SALES_VOLUME ELSE 0 END AS 
    TOTAL_SALES_VOLUME FROM CTE1 A INNER JOIN
    (SELECT CUSTOMERID ,
    SUM(sales_volume) total_sales_volume
    FROM SALES
    GROUP BY CUSTOMERID ) 
X ON A.CUSTOMERID = X.CUSTOMERID;
user_109082740
Related