CODEWITHSUNDEEP
mysqlsql
user1793864
user1793864

Reputation: 185

MySQL group by query with additional row

I have a table like below called trades

traders   qty

abc      50000
xyz      35000
pqr      25000
xxx      10000
yyy      20000
uuu      15000
abc      2000
xyz      3000
pqr      1500

I need to get the top 3 qty wise traders and all others in a separate row.

SELECT trader,SUM(qty)
FROM traders
GROUP BY trader DESC
LIMIT 3

from the above query I can get an answer like below

tr        qty

abc      52000
xyz      38000
pqr      26500

but my requirement is as below

tr        qty

abc      52000
xyz      38000
pqr      26500
other    45000   ----- with this column for all other traders except the largest 3

Upvotes: 1

Views: 93

Answers (2)

Olaf Dietsche
Olaf Dietsche

Reputation: 74008

Here's another one:

(select trader, sum(qty)
 from trades
 group by trader
 order by sum(qty) desc
 limit 3)
union
(select 'other', sum(qty)
 from (select trader, sum(qty) qty
       from trades
       group by trader
       order by sum(qty) desc
       limit 3, 4000000000) t);

SQL Fiddle for playing around.

Upvotes: 2

Devart
Devart

Reputation: 121902

Try this query -

SELECT
  IF(t2.trader IS NULL, 'other', t1.trader) trader,
  SUM(qty) qty
FROM traders t1
  LEFT JOIN (
  SELECT trader, SUM(qty) FROM traders
    GROUP BY trader
    ORDER BY SUM(qty) DESC
    LIMIT 3) t2
  ON t1.trader = t2.trader
GROUP BY trader
ORDER BY IF(t2.trader IS NULL, 1, 0), qty DESC

+--------+-------+
| trader | qty   |
+--------+-------+
| abc    | 52000 |
| xyz    | 38000 |
| pqr    | 26500 |
| other  | 45000 |
+--------+-------+

Upvotes: 3

Related Questions