MySQL Error: Subquery returns more than one row

In my query I am fetching three columns (say A,B and Summary)

Summary is based on B and 开发者_开发知识库A.

I have to use a subquery to fetch Summary based on A and B.

Problem:

For one value of A (say A1) and one value of B (say B1), I get two values of Summary (say Summ1 and Summ2) in One column.

Hence it gives Error:

 Subquery returns more than one row 

Instead I want to show it as below in my table:

 A1     B1     Summ1
 A1     B1     Summ2  

How can I do so?

QUERY:

SELECT a.FundIDRecv,a.SubscribeDt, b.FundName, 
(
   SELECT c.PricePerWeek
   FROM tbl_Hive c
   WHERE c.FundID IN 
   (
      SELECT FundID from tbl_FundStatic 
      WHERE FundID IN
      (
          SELECT FundIDSend
          FROM tbl_FundSubscriptions 
          WHERE FundIDRecv = a.FundIDRecv

      )
      AND UserID = '14'
   )
) as Price
FROM tbl_FundSubscriptions a, tbl_Hive b
WHERE a.FundIDRecv = b.FundID

AND a.FundIDRecv
IN (

SELECT FundIDRecv
FROM tbl_FundSubscriptions
WHERE FundIDSend
IN (

SELECT FundID
FROM tbl_FundStatic 
WHERE UserID = '14'
)
)
Group by a.FundIDRecv

SELECT statement for c.PricePerWeek generates that error

---

You dont need to use subquery to sum column A and B.

just try

SELECT A,B,(A+B) as 'sum'
FROM table

Post your query to help you more.

UPDATE

in your query it is normal that in subquery result return more than one row, maybe you wanted to do it like that: SUM(c.PricePerWeek)

SELECT a.FundIDRecv,a.SubscribeDt, b.FundName, 
(
   SELECT SUM(c.PricePerWeek)
   FROM tbl_Hive c
   WHERE c.FundID IN 
   (
      SELECT FundID from tbl_FundStatic 
      WHERE FundID IN
      (
          SELECT FundIDSend
          FROM tbl_FundSubscriptions 
          WHERE FundIDRecv = a.FundIDRecv

      )
      AND UserID = '14'
   )
) as Price
....

---

It sounds like you need to restructure it as a JOIN rather than a subquery.

---

Keep your subquery in the from clause and try....

Try this...

 SELECT a.FundIDRecv,a.SubscribeDt, b.FundName, d.price

FROM tbl_FundSubscriptions a, tbl_Hive b,
(  

  SELECT c.PricePerWeek as Price   FROM tbl_Hive c   
   WHERE c.FundID IN  
   (  
      SELECT FundID from tbl_FundStatic
       WHERE FundID IN     
        (
             SELECT FundIDSend
             FROM tbl_FundSubscriptions ,tbl_FundSubscriptions a 
             WHERE FundIDRecv = a.FundIDRecv
        )
        AND UserID = '14' 
 )

 ) d


 WHERE a.FundIDRecv = b.FundID 
 AND a.FundIDRecv IN ( 
 SELECT FundIDRecv FROM   tbl_FundSubscriptions
 WHERE FundIDSend IN 
 (
      SELECT FundID 
      FROM tbl_FundStatic
      WHERE UserID = '14' 
 )
 )
 Group by a.FundIDRecv 

---

Basically, I see it like this:

SELECT
  a.FundIDRecv,
  a.SubscribeDt,
  b.FundName,
  b.PricePerWeek as Price
FROM tbl_FundSubscriptions a, tbl_Hive b
WHERE a.FundIDRecv = b.FundID
  AND a.FundIDRecv IN (
    SELECT FundIDRecv
    FROM tbl_FundSubscriptions
    WHERE FundIDSend IN (
      SELECT FundID
      FROM tbl_FundStatic 
      WHERE UserID = '14'
    )
  )
Group by a.FundIDRecv, b.PricePerWeek

The old-fashioned join syntax I would probably replaced by the currently standard one. Also there seems to be one redundant IN subselect.

So eventually the script might look like this:

SELECT
  a.FundIDRecv,
  a.SubscribeDt,
  b.FundName,
  b.PricePerWeek as Price
FROM tbl_FundSubscriptions a
  INNER JOIN tbl_Hive b ON a.FundIDRecv = b.FundID
WHERE a.FundIDSend IN (
  SELECT FundID
  FROM tbl_FundStatic 
  WHERE UserID = '14'
)
Group by a.FundIDRecv, b.PricePerWeek