Group all rows after nth row together

Question

I have the current table:

+----------+-------+
| salesman | sales |
+----------+-------+
|        1 |   142 |
|        2 |   120 |
|        3 |   176 |
|        4 |   140 |
|        5 |   113 |
|        6 |   137 |
|        7 |   152 |
+----------+-------+

I would like to make a query to retrieve the 3 top salesman, and an "Other" column, that would be the sum of everyone else. The expected output would be:

+----------+-------+
| salesman | sales |
+----------+-------+
| 3        |   176 |
| 7        |   152 |
| 1        |   142 |
| Others   |   510 |
+----------+-------+

I am using MySQL, and I am experienced about it, but i can't imagine a way of doing this kind of GROUP BY.

A tried UNION with 2 SELECT, one for the top 3 salesman and another select for the "Others", but I couldn't figure a way of excluding the top 3 from the 2nd SELECT

Why don't you share that attempt with us, thus demonstrating a valid attempt at solving your question? — Shadow, Apr 16 '19 at 12:55

score 3 · Accepted Answer · answered Apr 16 '19 at 13:04

3

You can do this by LEFT JOINing your table to a list of the top 3 salesmen, and then grouping on the COALESCEd salesman number from the top 3 table (which will be NULL if the salesman is not in the top 3).

SELECT COALESCE(top.sman, 'Others') AS saleman,
       SUM(sales) AS sales
FROM test
LEFT JOIN (SELECT salesman AS sman
           FROM test
           ORDER BY sales DESC
           LIMIT 3) top ON top.sman = test.salesman
GROUP BY saleman
ORDER BY saleman = 'Others', sales DESC

Output:

saleman sales
3       176
7       152
1       142
Others  510

Demo on dbfiddle

answered Apr 16 '19 at 13:04

Nick

138,499
22
57
95

Exactly what I was looking for. Kudos for beeing in a single select and very simple, yet, creative – Rodolfo Donã Hosp Apr 16 '19 at 13:11
@RodolfoDonãHosp thanks. It's not really a single select though as there is the derived table as well. – Nick Apr 16 '19 at 13:13

score 2 · Answer 2 · answered Apr 16 '19 at 12:54

Using UNION, ORDER BY, LIMIT, OFFSET AND GROUP BY statements you should do the trick:

SELECT salesman, sales 
FROM t 
ORDER BY sales DESC LIMIT 3
UNION
SELECT 'Others', SUM(sales) 
FROM (SELECT salesman, sales 
      FROM t 
      ORDER BY sales DESC LIMIT 3, 18446744073709551615) AS tt;

The big number at the end is the way to apply limit until the end of the table, as suggested here

score 2 · Answer 3 · answered Apr 16 '19 at 12:55

This is a pain in MySQL:

(select salesman, count(*) as cnt
 from t
 group by salesman
 order by count(*), salesman
 limit 3
) union all
(select 'Others', count(*)
 from t left join
      (select salesman, count(*) as cnt
       from t
       group by salesman
       order by count(*)
       limit 3
      ) t3
      on t3.salesman = t.salesman
 where t3.salesman is null
);

Salman A · Answer 4 · 2019-04-16T13:43:47.643

This should be the fastest one if appropriate indexes are present:

(
    SELECT salesman, sales
    FROM t
    ORDER BY sales DESC
    LIMIT 3
)
UNION ALL
(
    SELECT 'Other', SUM(sales) - (
        SELECT SUM(sales)
        FROM (
            SELECT sales
            FROM t
            ORDER BY sales DESC
            LIMIT 3
        ) AS top3
    )
    FROM t
)
ORDER BY CASE WHEN salesman = 'Other' THEN NULL ELSE sales END DESC

Nikhil S · Answer 5 · 2019-04-16T13:09:12.133

0

this will work:

select salesman,sales from tablename a where a.salesman in (3,7,1) 
union all
select 'others' as others,sum(a.sales) as sum_of_others from tablename a where 
a.salesman not in (3,7,1) group by others;

check https://www.db-fiddle.com/f/73GjFXL3KsZsYnN26g3rS2/0

edited Apr 16 '19 at 13:09

answered Apr 16 '19 at 12:51

Nikhil S

3,786
4
18
32

I hope in the second select, it should be `select 'Others' as salesman,` – Arulkumar Apr 16 '19 at 12:53
1

Use no hard-coded id's... _top 3 salesman_ and _excluding the top 3_... – jarlh Apr 16 '19 at 12:57

Group all rows after nth row together

5 Answers5