Top sales making product for each year

Question

I have two tables with following schema.

CREATE TABLE Product (
  id INT AUTO_INCREMENT,
  name VARCHAR(64) UNIQUE,
  unit_price VARCHAR(6),
  PRIMARY KEY (id)
);

CREATE TABLE Sale (
  id INT AUTO_INCREMENT,
  product_id INT, created_at DATETIME,
  units INT, PRIMARY KEY (id),
  FOREIGN KEY (product_id) REFERENCES Product(id)
);

INSERT INTO Product(name, unit_price)
VALUES
('Methadone', 30),
('Codeine', 20),
('Morphine', 40),
('Fentanyl', 10);


INSERT INTO Sale(product_id, created_at, units)
VALUES
(1, '2004-01-06 08:27:25', 8),
(4, '2004-03-05 05:21:15', 24),
(2, '2005-01-06 08:26:55', 3),
(4, '2005-03-31 01:55:25', 6),
(1, '2005-11-09 11:33:20', 2),
(4, '2006-02-15 10:46:30', 4),
(3, '2006-04-14 07:40:50', 1),
(4, '2008-01-06 08:27:25', 5),
(4, '2008-01-10 21:31:45', 3),
(1, '2008-01-28 16:07:30', 9),
(3, '2008-03-05 05:21:00', 4),
(2, '2008-03-08 18:25:50', 5),
(4, '2008-05-02 02:15:20', 1),
(4, '2008-05-05 15:19:40', 3),
(4, '2008-06-28 23:08:55', 2),
(4, '2008-07-02 12:14:00', 2),
(3, '2008-08-29 09:07:50', 1),
(2, '2008-10-22 16:56:20', 5),
(3, '2008-10-26 06:01:25', 2),
(4, '2008-12-19 13:49:55', 4),
(2, '2008-12-23 02:55:30', 4),
(1, '2009-02-15 10:43:45', 4),
(2, '2009-04-14 07:37:50', 6),
(3, '2009-06-11 04:31:25', 3),
(4, '2009-08-08 01:25:30', 12),
(1, '2010-11-09 11:33:20', 8),
(1, '2011-01-14 10:22:35', 3),
(3, '2011-03-13 07:16:10', 3),
(4, '2011-05-10 04:10:15', 4),
(4, '2011-07-07 01:04:35', 4),
(4, '2011-09-02 21:58:10', 3),
(2, '2011-10-30 18:51:45', 3),
(1, '2011-12-27 15:45:20', 1),
(4, '2012-02-23 12:43:25', 2),
(4, '2012-04-21 09:37:30', 3),
(4, '2012-06-18 06:31:20', 1),
(2, '2012-08-15 03:25:40', 4),
(2, '2013-01-14 10:23:20', 4),
(4, '2013-02-01 05:01:35', 1),
(4, '2013-03-13 07:16:55', 1),
(4, '2013-03-31 01:55:10', 2),
(4, '2013-05-10 04:11:15', 2),
(3, '2013-05-27 22:48:45', 3),
(1, '2013-07-07 01:05:20', 3),
(2, '2013-07-24 19:42:20', 4),
(4, '2013-09-02 21:59:40', 4),
(3, '2013-09-20 16:36:10', 1),
(4, '2013-10-30 18:54:00', 2),
(4, '2013-11-17 13:29:45', 1),
(4, '2013-12-27 15:48:20', 3);

I want to find top sales making product for each year sorted by year in descending order along-with the total sale made for the top selling product.

I have written the following query.

SELECT EXTRACT(YEAR FROM S.created_at) as year,
GROUP_CONCAT(DISTINCT P1.name
        ORDER BY P1.name
        SEPARATOR ',')as top,
        MAX(S.units*P1.unit_price) as sale

FROM Sale S
INNER JOIN Product P1 ON S.product_id=P1.id
GROUP BY year

ORDER BY year desc

Expected Output:

2013    Codeine,Fentanyl,Morphine   160
2012    Codeine 80
2011    Methadone,Morphine  120
2010    Methadone   240
2009    Codeine,Fentanyl,Methadone,Morphine 120
2008    Codeine,Morphine    280
2006    Fentanyl,Morphine   40
2005    Codeine,Fentanyl,Methadone  60
2004    Fentanyl,Methadone  240

Original Output:

2013    Codeine,Fentanyl,Methadone,Morphine 120
2012    Codeine,Fentanyl    80
2011    Codeine,Fentanyl,Methadone,Morphine 120
2010    Methadone   240
2009    Codeine,Fentanyl,Methadone,Morphine 120
2008    Codeine,Fentanyl,Methadone,Morphine 270
2006    Fentanyl,Morphine   40
2005    Codeine,Fentanyl,Methadone  60
2004    Fentanyl,Methadone  240

The query was supposed to return only the top selling products, bu it's returning all the products. Could anyone provide any help?

can you share some sample data? Adding insert statements to your question would be helpful — Standin.Wolf, Dec 09 '19 at 17:35
@Strawberry I have added sample data for both the tables. Please check. — theluckiestsoul, Dec 09 '19 at 17:45

GMB · Answer 1 · 2019-12-09T18:07:02.003

Here is a solution for MySQL < 8.0. First, aggregate sales by year and product and filter on the top selling products with an aggregate, correlated subquery; then, aggregate by year:

select
    year_at,
    group_concat(name order by name) products,
    total_sales
from (
    select
        year(s.created_at) year_at,
        p.name,
        sum(s.units * p.unit_price) total_sales
    from Product p
    inner join Sale s on s.product_id = p.id
    group by year_at, p.id, p.name      
    having total_sales = (
        select sum(s1.units * p1.unit_price) total_sales1
        from Product p1
        inner join Sale s1 on s1.product_id = p1.id
        where year(s1.created_at) = year_at
        group by s1.product_id
        order by total_sales1 desc
        limit 1
    )
) t
group by year_at, total_sales
order by year_at desc

Demo on DB Fiddlde:

year_at | products                            | total_sales
------: | :---------------------------------- | ----------:
   2013 | Codeine,Fentanyl,Morphine           |         160
   2012 | Codeine                             |          80
   2011 | Methadone,Morphine                  |         120
   2010 | Methadone                           |         240
   2009 | Codeine,Fentanyl,Methadone,Morphine |         120
   2008 | Codeine,Morphine                    |         280
   2006 | Fentanyl,Morphine                   |          40
   2005 | Codeine,Fentanyl,Methadone          |          60
   2004 | Fentanyl,Methadone                  |         240

Yes, I was looking for this. Thank you so much @GMB. – theluckiestsoul Dec 09 '19 at 18:11 — theluckiestsoul, Dec 09 '19 at 18:11

Strawberry · Answer 2 · 2019-12-09T18:07:12.833

You seem to be after this...

SELECT GROUP_CONCAT(a.name) names,a.total, a.year
  FROM 
     ( SELECT p.name 
            , SUM(p.unit_price*s.units) total
            , YEAR(created_at) year 
         FROM product p 
         JOIN sale s 
           ON s.product_id = p.id 
        GROUP 
           BY year
            , name
     ) a
  JOIN
     ( SELECT MAX(total) total
      , year 
   FROM 
      ( SELECT p.name 
             , SUM(p.unit_price*s.units) total
             , YEAR(created_at) year 
          FROM product p 
          JOIN sale s 
            ON s.product_id = p.id 
         GROUP 
            BY year
             , name
      ) n
  GROUP 
     BY year) b
    ON b.year = a.year AND b.total = a.total
  GROUP BY a.total, a.year
  ORDER BY year DESC;
+-------------------------------------+-------+------+
| names                               | total | year |
+-------------------------------------+-------+------+
| Fentanyl,Codeine,Morphine           |   160 | 2013 |
| Codeine                             |    80 | 2012 |
| Morphine,Methadone                  |   120 | 2011 |
| Methadone                           |   240 | 2010 |
| Morphine,Methadone,Fentanyl,Codeine |   120 | 2009 |
| Morphine,Codeine                    |   280 | 2008 |
| Morphine,Fentanyl                   |    40 | 2006 |
| Codeine,Methadone,Fentanyl          |    60 | 2005 |
| Methadone,Fentanyl                  |   240 | 2004 |
+-------------------------------------+-------+------+

This does produce the expected results as well. – GMB Dec 10 '19 at 01:00 — GMB, Dec 10 '19 at 01:00

score 1 · Accepted Answer · edited Dec 10 '19 at 16:10

Here's a variant of my Scalar-Aggregate Comparison query technique that will achieve what you want in a vastly better-performing query than self-joining the aggregate results. As you can see below, this only ever performs a single join, then 3 cascaded layers of aggregation to achieve the final output.

SELECT
    yr,
    SUBSTRING(MAX(CONCAT(LPAD(total_sales, 16, '0'), products)), 17) AS best_seller,
    MAX(total_sales) AS sales_amount
FROM (
    SELECT
        yr,
        total_sales,
        GROUP_CONCAT(name order by name) AS products
    FROM (
        SELECT
            YEAR(s.created_at) AS yr,
            p.id,
            p.name,
            SUM(s.units * p.unit_price) AS total_sales
        FROM Product AS p
        INNER JOIN Sale AS s on s.product_id = p.id
        GROUP BY YEAR(s.created_at), p.id
    ) AS pys
    GROUP BY yr, total_sales
) AS py
GROUP BY yr
ORDER BY yr DESC;

Here's a DB Fiddle demo: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=e14ed7f396738ce064f1998bdb4310ac

Hi @Steven Moseley, thanks for the solution. It's also giving the same result. — theluckiestsoul, Dec 10 '19 at 06:18
Thanks @Steven Moseley, I have accepted the answer after adding "order by" in GROUP_CONCAT() — theluckiestsoul, Dec 10 '19 at 15:16

score 0 · Answer 4 · answered Dec 09 '19 at 17:35

0

try this:

Select name, year(created_at), sum(unit_price*units ) 
from Product p inner join sale s on p.id = s.product_id 
group by name, year(created_at)
order by 2, 3 desc

answered Dec 09 '19 at 17:35

zip

3,938
2
11
19

Have edited my question. Could you please check again? – theluckiestsoul Dec 09 '19 at 17:41

score 0 · Answer 5 · answered Dec 09 '19 at 17:46

If you want the top selling product for each year, use rank() and an aggregation query:

select ps.*
from (select p.name, year(s.created_at) as year, sum(p.unit_price*s.units) as sales
             rank() over (partition by year(s.created_at) order by sum(p.unit_price*s.units) desc as seqnum
      from Product p inner join
           sale s
           on p.id = s.product_id 
      group by name, year
     ) ps
where seqnum <= 1;  -- You can change "1" for more rows

Top sales making product for each year

5 Answers5