Solved

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

Posted on 2011-02-14
3
542 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 32

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

773 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