Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can handle this ERROR 1242 (21000): Subquery returns more than 1 row

Tags:

sql

mysql

Hello everyone I am having issue with SQL query, have googled a lot but couldnt find any solution. here is my code:

 SELECT (SELECT ltp 
         FROM eod_stock  
         WHERE company_id IN (1,2) 
             AND entry_date = '2013-09-24') AS b,
        (SELECT ltp 
         FROM eod_stock  
         WHERE company_id IN (1,2) 
             AND entry_date = '2013-09-25') AS e,
        CAST(((SELECT ltp 
               FROM eod_stock  
               WHERE company_id IN(1,2) 
                   AND entry_date = '2013-09-25') -
              (SELECT ltp 
               FROM eod_stock  
               WHERE company_id IN (1,2) 
                   AND entry_date = '2013-09-24')) /
              (SELECT ltp  
               FROM eod_stock  
               WHERE company_id IN (1,2) 
                   AND entry_date = '2013-09-25')*100 
             AS DECIMAL(10,2)) AS c;

it gives me error

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

I understand the error cause my query should return more than 1 row as i am passing 2 company but i need all companies ltp, that i mentioned in my query. The result is need is like:

+--------+--------+--------+
| b      | e      | c      |
+--------+--------+--------+
| 460.00 | 338.00 | -36.09 |
+--------+--------+--------+
| 320.00 | 488.00 |  34.42 |
+--------+--------+--------+

can anyone suggest how can i get multiple result like this from my query? caz i have to pass more than 1 company id in my query.

Table structure:

+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| company_id      | varchar(30)      | NO   | PRI | NULL    |       |
| entry_date      | date             | NO   | PRI | NULL    |       |
| entry_timestamp | int(10) unsigned | NO   |     | NULL    |       |
| open            | decimal(16,2)    | NO   |     | NULL    |       |
| high            | decimal(16,2)    | NO   |     | NULL    |       |
| low             | decimal(16,2)    | NO   |     | NULL    |       |
| ltp             | decimal(16,2)    | NO   |     | NULL    |       |
| ycp             | decimal(16,2)    | NO   |     | NULL    |       |
| cse_price       | decimal(9,2)     | NO   |     | NULL    |       |
| cse_volume      | decimal(18,2)    | NO   |     | NULL    |       |
| total_trade     | int(30)          | NO   |     | NULL    |       |
| total_volume    | int(30)          | NO   |     | NULL    |       |
| total_value     | decimal(18,4)    | NO   |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+
like image 214
Samia Ruponti Avatar asked Sep 05 '25 03:09

Samia Ruponti


1 Answers

When you select from a subquery you can only get a single row.

You can join the table on itself and select the rows with the two dates you are interested in. Something like this should work.

SELECT a.ltp, b.ltp, CAST(((b.ltp - a.ltp) / b.ltp * 100) AS DECIMAL(10, 2))
FROM eod_stock a
JOIN eod_stock b ON a.company_id = b.company_id
WHERE a.entry_date = "2013-09-24"
AND b.entry_date = "2013-09-25"
AND a.company_id IN (1, 2)
like image 164
mcrumley Avatar answered Sep 07 '25 21:09

mcrumley