How to add multiple limits in mysql query?

Question

I want to select SUM of last 200,100 and 50 rows of close price from a table in a single query. given below is the query for 200 rows.

SELECT SUM(Close_price) as tot200 FROM cash_data ORDER BY date_added LIMIT 0,200

can anyone help me with this.

@ juergen d i just edited my question please check it now. I want the result like this tot200 ,tot100 and tot50. — ami_nedved
– ami_nedved, Commented Aug 19, 2015 at 12:24

Sougata Bose · Accepted Answer · 2015-08-19 12:28:42Z

3

You can use sub queries -

SELECT 
(SELECT SUM(Close_price) FROM cash_data ORDER BY date_added LIMIT 0,200) as tot200, 
(SELECT SUM(Close_price) FROM cash_data ORDER BY date_added LIMIT 0,100)  as tot100,
(SELECT SUM(Close_price) FROM cash_data ORDER BY date_added LIMIT 0,50)  as tot50

answered Aug 19, 2015 at 12:28

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

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

Comments

juergen d · Accepted Answer · 2015-08-19 12:58:55Z

1

To make it in a single select without any subselects you can use a ranking varaible and sum conditionally

SELECT @rank := @rank + 1, 
       SUM(case when @rank <= 50 then Close_price else 0 end) as top_50_sum, 
       SUM(case when @rank <= 100 then Close_price else 0 end) as top_100_sum,
       SUM(case when @rank <= 200 then Close_price else 0 end) as top_200_sum
FROM cash_data 
cross join (select @rank := 0) r
ORDER BY date_added 
LIMIT 0,200

edited Aug 19, 2015 at 12:58

answered Aug 19, 2015 at 12:27

juergen d

205k40 gold badges305 silver badges377 bronze badges

1 Comment

ami_nedved Over a year ago

thanks @ juergen d. i will try it and let you know the result.

ami_nedved · Accepted Answer · 2015-08-20 05:55:56Z

Thanks for all the replies. Actually both the answers are working but i choose the second one and made some changes for my need. Below is the final query

SELECT 
(SELECT SUM(Close_price)/200 FROM (SELECT Close_price FROM cash_data WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added <= '$date' ORDER BY date_added DESC LIMIT 0,200) as tot200) as avg200, 
(SELECT SUM(Close_price)/100 FROM (SELECT Close_price FROM cash_data WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added <= '$date' ORDER BY date_added DESC LIMIT 0,100) as tot100) as avg100,
(SELECT SUM(Close_price)/50 FROM (SELECT Close_price FROM cash_data WHERE SERIES='$series' AND SYMBOL='$symbol' AND date_added <= '$date' ORDER BY date_added DESC LIMIT 0,50) as tot50) as avg50

Thank you all.

Collectives™ on Stack Overflow

How to add multiple limits in mysql query?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related