Question 22200 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

4

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
  4. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search