Calculate averages if values are in different rows in the table

user_4934150 0
Viewed 40

DB-Fiddle

CREATE TABLE sales (
    id int primary key,
    category VARCHAR(255),
    event_date DATE,
    sales_volume VARCHAR(255), 
    return_volume VARCHAR(255)
);

INSERT INTO sales
(id, category, event_date, 
sales_volume, return_volume
)
VALUES 
("1", "CAT_01", "2018-05-30", "500", NULL),
("2", "CAT_01", "2018-06-05", NULL, "450"),
("3", "CAT_02", "2019-07-08", "700", NULL),
("4", "CAT_02", "2019-07-18", NULL, "670");

The table displays the sales and returns per category.

Now I want to calculate the return_rate by returns/sales.
The result should look like this:

Category      sales_volume      return_volume       return_rate
CAT_01           500               450                0.90
CAT_02           700               670                0.96

I tried to go with this query:

SELECT
category,
sales_volume,
return_volume,
return_volume / sales_volume as return_rate
FROM sales;

However, when I run this query I get NULL for the return_rate.
I assume this issue is casued because the return_volume is in a different row than the sales_volume.

How do I have to modify my query to get the expected result?

mysql sql
3 Answers

After checking everything again I figured out that HeidiSQL on my computer runs the query through even if it looks like this:

SELECT
category,
sales_volume,
return_volume,
sum(return_volume) / sum(sales_volume) as return_rate
FROM sales;

The result is this:

Category      sales_volume      return_volume       return_rate
CAT_01           0                  0                 0.90
CAT_02           0                  0                 0.96

When I run this query in the DB - Fiddle it does not get through because the SUM for sales_volume and return_volume as well as the GROUP BY 1 is missing.

Once I modify my query with it looking like this:

SELECT
category,
sum(sales_volume),
sum(return_volume),
sum(return_volume) / sum(sales_volume) as return_rate
FROM sales
GROUP BY 1;

I will also get the correct result here DB-Fiddle.

Of course the other answers to this question work as well.

user_49341500

Use coalesce() to replace null values & do aggregation :

SELECT category, 
       SUM(coalesce(sales_volume, 0)) AS sales_volume, 
       SUM(coalesce(return_volume, 0)) AS return_volume,
       SUM(coalesce(return_volume, 0)) / SUM(coalesce(sales_volume, 0)) AS return_rate
FROM sales
group by category;
user_55294450

I guess you may try below query -

SELECT
category,
MAX(sales_volume),
MAX(return_volume),
MAX(return_volume) / MAX(sales_volume) as return_rate
FROM sales
GROUP BY category;
user_36277560
Related