MySQL group by query with addtional row

http://stackoverflow.com/questions/13305459/mysql-group-by-query-with-addtional-row/13305688#13305688

 

CREATE TABLE sales(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  product_id INT(11),
  qty INT(11)
);

INSERT INTO sales VALUES
  (NULL, 1, 500),
  (NULL, 1, 300),
  (NULL, 1, 400),
  (NULL, 2, 1000),
  (NULL, 2, 500),
  (NULL, 3, 10),
  (NULL, 3, 20),
  (NULL, 4, 1500),
  (NULL, 5, 15),
  (NULL, 5, 25),
  (NULL, 5, 10);

SELECT * FRPM sales;
+—-+————+——+
| id | product_id | qty  |
+—-+————+——+
|  1 |          1 |  500 |
|  2 |          1 |  300 |
|  3 |          1 |  400 |
|  4 |          2 | 1000 |
|  5 |          2 |  500 |
|  6 |          3 |   10 |
|  7 |          3 |   20 |
|  8 |          4 | 1500 |
|  9 |          5 |   15 |
| 10 |          5 |   25 |
| 11 |          5 |   10 |
+—-+————+——+

Desired output:
+————+———-+
| product_id | quantity |
+————+———-+
| 4          |     1500 |
| 2          |     1500 |
| 1          |     1200 |
| other      |       80 |
+————+———-+

SELECT
  product_id FROM sales
GROUP BY
  product_id
ORDER BY
  SUM(qty) DESC
LIMIT
  3;
+————+
| product_id |
+————+
|          2 |
|          4 |
|          1 |
+————+

Join two tables:

SELECT * FROM sales t1
LEFT JOIN (
  SELECT product_id FROM sales
  GROUP BY product_id
  ORDER BY SUM(qty) DESC
  LIMIT 3
  ) t2
  ON t1.product_id = t2.product_id;
+—-+————+——+————+
| id | product_id | qty  | product_id |
+—-+————+——+————+
|  1 |          1 |  500 |          1 |
|  2 |          1 |  300 |          1 |
|  3 |          1 |  400 |          1 |
|  4 |          2 | 1000 |          2 |
|  5 |          2 |  500 |          2 |
|  6 |          3 |   10 |       NULL |
|  7 |          3 |   20 |       NULL |
|  8 |          4 | 1500 |          4 |
|  9 |          5 |   15 |       NULL |
| 10 |          5 |   25 |       NULL |
| 11 |          5 |   10 |       NULL |
+—-+————+——+————+

Add GROUP BY and –

SELECT
  IF(t2.product_id IS NULL, \’other\’, t1.product_id) product_id,
  SUM(qty) quantity
FROM sales t1
  LEFT JOIN (
  SELECT product_id FROM sales
    GROUP BY product_id
    ORDER BY SUM(qty) DESC
    LIMIT 3) t2
  ON t1.product_id = t2.product_id
GROUP BY product_id
ORDER BY t2.product_id DESC, qty DESC;

+————+———-+
| product_id | quantity |
+————+———-+
| 4          |     1500 |
| 2          |     1500 |
| 1          |     1200 |
| other      |       80 |
+————+———-+

Leave a Reply

Your email address will not be published. Required fields are marked *