I want to be able to compare revenue for products from two different dates from the same table-CodePudding

So I can run two separate queries like this:

SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
FROM product_inventory 
WHERE date = '2021-11-17' 
GROUP BY date1 , product1, product_id_1 
ORDER BY rev1 DESC

SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
FROM product_inventory 
WHERE date = '2022-11-17' 
GROUP BY date2 , product2, product_id_2 
ORDER BY rev2 DESC

And this is the output I get for each:

date1	product1	product_id_1	rev1
2021-11-17	adidas samba	9724	6087.7000732421875
2021-11-17	nike air max	5361	4918.0
2021-11-17	puma suede	1985	3628.1600341796875

date2	product2	product_id_2	rev2
2022-11-17	adidas samba	9724	5829.0
2022-11-17	nike air max	5361	4841.864013671875
2022-11-17	puma suede	1985	5404.4140625

How can I query the db in a way that would pull the date2 and rev2 column into one single output like this?

date1	product1	product_id_1	rev1	date2	rev2
2021-11-17	adidas samba	9724	6087.7000732421875	2022-11-17	5829.0
2021-11-17	nike air max	5361	4918.0	2022-11-17	4841.864013671875
2021-11-17	puma suede	1985	3628.1600341796875	2022-11-17	5404.4140625

I tried this query:

SELECT A.date1, A.product1, A.rev1, B.date2, B.product2, B.rev2 FROM
(
SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 FROM product_inventory WHERE date = '2021-11-17' GROUP BY date1 , product1, product_id_1 ORDER BY rev1 DESC
) A,
(
SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 FROM product_inventory WHERE date = '2022-11-17' GROUP BY date2, product2, product_id_2 ORDER BY rev2 DESC
) B;

but I get this output

date1	product1	rev1	date2	product2	rev2
2021-11-17	puma suede	3628.1600341796875	2022-11-17	adidas samba shoes	5829.0
2021-11-17	nike air max	4918.0	2022-11-17	adidas samba shoes	5829.0
2021-11-17	adidas samba	6087.7000732421875	2022-11-17	adidas samba shoes	5829.0
2021-11-17	puma suede	3628.1600341796875	2022-11-17	puma suede	5404.4140625
2021-11-17	nike air max	4918.0	2022-11-17	puma suede	5404.4140625
2021-11-17	adidas samba	6087.7000732421875	2022-11-17	puma suede	5404.4140625
2021-11-17	puma suede	3628.1600341796875	2022-11-17	nike air max	4841.864013671875
2021-11-17	nike air max	4918.0	2022-11-17	nike air max	4841.864013671875
2021-11-17	adidas samba	6087.7000732421875	2022-11-17	nike air max	4841.864013671875

It's like the number of records gets squared.

CodePudding user response：

You need to use JOIN on product_id like this:

WITH DATE_1 AS (
SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
  FROM product_inventory 
 WHERE date = '2021-10-17' 
 GROUP BY 1, 2, 3
),
DATE_2 AS (
SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
  FROM product_inventory 
 WHERE date = '2021-11-17' 
 GROUP BY 1, 2, 3
)
SELECT D1.*, D2.*
  FROM DATE_1 D1
       INNER JOIN DATE_2 D2
          ON D1.product_id_1 = D2.product_id_2

CodePudding user response：

Your first two queries are practically identical. Only the aliases in them are different. That makes the task a bit pointless. But if we assume you want to join the data from the two result sets, here is how you could do it:

SELECT t1.date, t1.product, t1.product_id, t2.date, t2.product_id, ...
FROM
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...) AS t1
JOIN
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...) AS t2
ON t1.product_id = t2.product_id AND t1.date = t2.date
ORDER BY ...

Also, MySQL 8 introduced a feature called Common Table Expressions (CTE), which might be a nice alternative to the above query, in that it allows you to separate the SELECT queries of the two result sets you want to join. So with CTE you could write something like this:

WITH 
  t1 AS
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...),
  t2 AS
  (SELECT ... FROM product_inventory WHERE ... GROUP BY ...),
SELECT t1.date, t1.product, t1.product_id, t2.date, t2.product_id, ...
FROM t1      
JOIN t2 ON t1.product_id = t2.product_id AND t1.date = t2.date
ORDER BY ...

Note that in both queries, I've extracted the ORDER BY from the two result sets and have put it in the "final" SELECT.

CodePudding user response：

You are getting a cartesian join of table A and B i.e all combination of rows from A and B, hence you see the records increase to 9 rows because of 3 x 3.

Wha you need to do is join of table A and B on product: For example a (inner) join:

SELECT A.*, B.*
FROM A
INNER JOIN B on A.product = B.product

Avoid using * in SELECT when possible.

CodePudding user response：

Using your informations, you can do the WITH from sql, it will generate "fake table" that you can use to do some others operation

-- Your first query that I put in a "fake table" named first_date
WITH first_date AS (
    SELECT date as date1, product as product1, product_id as product_id_1, SUM(revenue) AS rev1 
    FROM product_inventory 
    WHERE date = '2021-11-17' 
    GROUP BY date1 , product1, product_id_1 
    ORDER BY rev1 DESC
),
-- Your second query that I put in a "fake table" named second_date
second_date AS (
    SELECT date as date2, product as product2, product_id as product_id_2, SUM(revenue) AS rev2 
    FROM product_inventory 
    WHERE date = '2022-11-17' 
    GROUP BY date2 , product2, product_id_2 
    ORDER BY rev2 DESC
)
-- First we get the products in both "fake tables"
SELECT a.*, b.date2, b.rev2
FROM first_date a
INNER JOIN second_date b ON a.product_id_1 = b.product_id_2
UNION
-- Then only in the first "fake table"
SELECT c.*, NULL, NULL
FROM first_date c
LEFT JOIN second_date d ON c.product_id_1 = d.product_id_2
WHERE d.product_id_2 IS NULL
UNION
-- Then only in the second "fake table"
SELECT NULL, f.product2, f.product_id_2, NULL, f.date2, f.rev2
FROM first_date e
RIGHT JOIN second_date f ON e.product_id_1 = f.product_id_2
WHERE e.product_id_1 IS NULL;

CodePudding user response：

You want to compare the revenues of the same product on two different dates. You can do this without subqueries or WITH, using conditional aggregation:

select product_id, product,
    sum(case when date = '2021-11-17' then revenue end) as rev_2021_11_17,
    sum(case when date = '2022-11-17' then revenue end) as rev_2022_11_17
from product_inventory 
where date in ('2021-11-17', '2022-11-17')
group by product_id, product

I don't really see the need to return the dates in the resultset; they are know to the client already, since they are given as paramters to the query. But it you like, you either hardcode them in the SELECT clause, or use MIN and MAX.