Solved

Getting error : #1242 - Subquery returns more than 1 row

Posted on 2011-02-14
3
539 Views
Last Modified: 2013-12-13
What should I do to correct this query.
SELECT `Index` , `FundName` ,Count(*), (SELECT COALESCE(sum(b.PricePerWeek),0)       
FROM tbl_FundSubscriptions      
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= SubscribeDt        GROUP BY FundIDSend)  
FROM tbl_FundSubscriptions b, tbl_FundStatic a  
WHERE a.FundID = b.FundIDSend  AND FundIDSend IN      (SELECT FundID      FROM tbl_FundStatic      WHERE UserID = '14')  
GROUP BY a.FundName,a.Index

Open in new window

0
Comment
Question by:meetpd
3 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 34887940
This part of your query:

(SELECT COALESCE(sum(b.PricePerWeek),0)      
FROM tbl_FundSubscriptions      
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= SubscribeDt        GROUP BY FundIDSend)

can only return 1 row.  Maybe try using SELECT DISTINCT or SELECT TOP 1.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34888193
GROUP BY GROUP BY FundIDSend in that subquery shall be replaced by
 AND FundSubscriptions.FundIDSend = b.FundIDSend
0
 
LVL 31

Expert Comment

by:awking00
ID: 34890119
Can you provide some sample data for the FundSubscriptions and FundStatic tables and the single row you want to be returned?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now