MySQL subquery error ERROR 1242 (21000): Subquery returns more than 1 row

Question

Hello, I want to order my query by mcap which is in the query but I'm getting error, I've googled but couldn't find suitable solution. Here's the query:

SELECT 

 c.code,
(e.total_value - 
IFNULL((SELECT total_value FROM eod_stock WHERE company_id = e.company_id AND entry_date =  

SELECT 
entry_date FROM eod_stock WHERE entry_date < e.entry_date AND company_id = e.company_id ORDER  

BY entry_date DESC LIMIT 0,1)),0))/

(SELECT total_value FROM eod_stock WHERE company_id = e.company_id AND entry_date = (SELECT entry_date FROM eod_stock WHERE entry_date < e.entry_date AND company_id = e.company_id ORDER BY entry_date DESC LIMIT 0,1)) AS turnover_growth,

(SELECT total_share FROM share_percentage WHERE company_code = c.code) * e.ltp AS mcap,

'' AS pe

 -- ,e.*

FROM eod_stock AS e

LEFT OUTER JOIN company AS c

ON c.ID = e.company_id

WHERE e.company_id AND e.entry_date = (SELECT MAX(entry_date) FROM eod_stock)

ORDER BY mcap DESC

LIMIT 0,10;

but it returns an error:

ERROR 1242 (21000): Subquery returns more than 1 row

Don't know what went wrong. Can anyone help me?

Gordon Linoff · Accepted Answer · 2014-01-06 17:17:28Z

2

The only subquery that is not a global aggregation or has a limit clause is:

(SELECT total_share FROM share_percentage WHERE company_code = c.code)

I would guess that this is returning more than one row.

EDIT:

How do you fix it? That depends on the logic. Here are two ways that look sensible to me:

(SELECT sum(total_share) FROM share_percentage WHERE company_code = c.code)

(SELECT total_share FROM share_percentage WHERE company_code = c.code limit 1)

edited Jan 6, 2014 at 17:17

answered Jan 6, 2014 at 17:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Samia Ruponti Over a year ago

i know but how can fix it?

Samia Ruponti Over a year ago

Yes second one did the trick. thanks. first is not logically right cal total_share =! sum(total_share), even though it gives MySQL error also it would generate wrong result.

Collectives™ on Stack Overflow

MySQL subquery error ERROR 1242 (21000): Subquery returns more than 1 row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related