How to apply window function/ or non window function in MySQL to solve below problem? [duplicate]

Question

The table below is for different brands of milk tea stores' profits. How to use MySQL to calculate the different profits between (milkA + milkB) and milkC based on each date. (how to use both non window function and window function to approach in such case)

Sample milk_tea table:

timeDate	brand_name	profit
2020-01-01	milkA	400
2020-01-01	milkB	200
2020-01-01	milkC	300
2020-01-02	milkA	200
2020-01-02	milkB	300
2020-01-02	milkC	100
....

For example: for timeDate 2020-01-01, the different is equal to (400 + 200) - 300 = 300;

400 is the profit of milkA(brand_name), 200 is the profit of milkB(brand_name), 300 is the profit of milkC(brand_name).

Notes:

1. Assume all columns have the correct types like timeDate in Date type.
2. There could be another brand_name milk tea stores like milkD, milkE.
3. Assume no null value in all columns

Sample code for defining the table in MySQL:

create table milk_tea(
   timeDate DATE,
   brand_name VARCHAR(100) NOT NULL,
   profit INT
);

INSERT INTO milk_tea VALUES ('2020-01-01', 'milkA', 400);
INSERT INTO milk_tea VALUES ('2020-01-01', 'milkB', 200);
INSERT INTO milk_tea VALUES ('2020-01-01', 'milkC', 300);
INSERT INTO milk_tea VALUES ('2020-01-02', 'milkA', 200);
INSERT INTO milk_tea VALUES ('2020-01-02', 'milkB', 300);
INSERT INTO milk_tea VALUES ('2020-01-02', 'milkC', 100);

Desired output:

timeDate	diff_profit
2020-01-01	300
2020-01-02	400
....

Can someone please provide me with some MySQL code? Thanks a lot

Answer 1

You want conditional aggregation (a boolean expression inside the aggregate function):

select
  timedate,
  sum(case when brand_name in ('milkA', 'milkB') then profit end) as profit_ab,
  sum(case when brand_name = 'milkC' then profit end) as profit_c,
  sum(case when brand_name = 'milkC' then -profit else profit end) as diff
from milk_tea
where brand_name in ('milkA', 'milkB', 'milkC') 
group by timedate
order by timedate;

Answer 2

You may use conditional aggregation here:

SELECT
    timeDate,
    MAX(CASE WHEN brand_name = 'milkA' THEN profit ELSE 0 END) +
    MAX(CASE WHEN brand_name = 'milkB' THEN profit ELSE 0 END) -
    MAX(CASE WHEN brand_name = 'milkC' THEN profit ELSE 0 END) AS diff_profit
FROM milk_tea
GROUP BY timeDate
ORDER BY timeDate;