MySQL query - limit function

Question

I'm using the below query to get datas from mysql database.

select component,count(component) as Quantity from mfgGroup where 
 period between '2013-01-01' and '2013-12-31'

I'm getting almost 100+ rows as a result.

Now i want to display only top 25 components.so I modified the query as below

select component,count(component) as Quantity from mfgGroup where 
period between '2013-01-01' and '2013-12-31' limit 0,25

But i what i need is to display the top 25 items and rest of the components should be tagged as 'Others' with the quantity numbers.

Like these below in the resultset.

Item1 123
Item2 112
....
....
Item25 24
Others 156

What is 156 in the Others line? Is it the number of other rows? — Barmar
– Barmar, Commented Feb 11, 2014 at 7:37

Barmar · Accepted Answer · 2014-02-11 07:42:22Z

3

SELECT component, COUNT(*) AS Quantity
FROM mfgGroup
WHERE period between '2013-01-01' and '2013-12-31'
GROUP BY component
ORDER BY Quantity DESC
LIMIT 25

UNION

SELECT 'Others', COUNT(*) c
FROM mfgGroup g
LEFT JOIN (
    SELECT component, COUNT(*) AS Quantity
    FROM mfgGroup
    WHERE period between '2013-01-01' and '2013-12-31'
    GROUP BY component
    ORDER BY Quantity DESC
    LIMIT 25
) top25
ON g.component = top25.component
WHERE top25.component IS NULL
AND period between '2013-01-01' and '2013-12-31'
HAVING C > 0

answered Feb 11, 2014 at 7:42

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pradeeshnarayan · Accepted Answer · 2014-02-11 07:55:28Z

1

You can try using row_number also.

SELECT component,sum(quantity) FROM(
SELECT @rn:=@rn+1 AS rank, IF(@rn>25,"Others",component) component, Quantity
FROM (
    SELECT component, COUNT(*) AS Quantity
    FROM mfgGroup
    WHERE period between '2013-01-01' and '2013-12-31'
    GROUP BY component
    ORDER BY Quantity DESC
) t1, (SELECT @rn:=0) t2
) temp
GROUP BY component

answered Feb 11, 2014 at 7:55

Pradeeshnarayan

1,23510 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL query - limit function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related