Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » How to GROUP BY other

How to GROUP BY other

Author: rootsql Date: 4-12-2012, 21:32

Suppose you have a sales table, and you are going to get top 3 quantity wise products and all others in a separate row.

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);
 

This is our desired result:

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

Let's start with a query which outputs 3 top products:

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

Join our table with this query:

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 |
+----+------------+------+------------+
 

We almost get desired result, the last field contains numbers of 3 top products and NULL for other. Now we just need to add aggregate functions:

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 |
+------------+----------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.