CODEWITHSUNDEEP
mysqlsql
LiveEn
LiveEn

Reputation: 3253

1242 - Subquery returns more than 1 row in subquery

I'm trying to get the total of pending amount of each staff. The below query works fine:

SELECT SUM(amount) 
FROM pending
WHERE MONTH < DATE_SUB(curdate() , INTERVAL 1 MONTH) 
GROUP BY class

but when I try to add it as a subquery it gives me the error below:

1242 - Subquery returns more than 1 row

SELECT
  (period_diff(date_format(now(), '%Y%m'),
     date_format(MONTH, '%Y%m'))) AS months,
  pending.amount,
  pending.admission_numb,
  pending.month,
  staff.name,
  staff.class, (
    SELECT SUM(amount) 
    FROM pending
    WHERE MONTH < DATE_SUB(curdate(), INTERVAL 1 MONTH) 
    GROUP BY class
  )
FROM
  pending JOIN staff
  ON pending.admission_numb = staff.admission
GROUP BY admission
ORDER BY CAST( staff.class AS UNSIGNED ) , staff.class

any help will be appreciated..

Upvotes: 0

Views: 277

Answers (2)

fthiella
fthiella

Reputation: 49089

Since your subquery returns more than one row (i expect that it will return a row for each class), you need do join your subquery in the from clause:

SELECT
  (period_diff(date_format(now(), '%Y%m'), date_format(MONTH, '%Y%m'))) AS months,
  pending.amount,
  pending.admission_numb,
  pending.month,
  staff.name,
  staff.class,
  sums.tot
FROM
  pending JOIN staff ON pending.admission_numb = staff.admission
  JOIN  (
    SELECT class, SUM(amount) as tot 
    FROM pending
    WHERE MONTH < DATE_SUB(curdate(), INTERVAL 1 MONTH)
    GROUP BY class
  ) sums on staff.class = sums.class
GROUP BY admission
ORDER BY CAST( staff.class AS UNSIGNED ) , staff.class

Upvotes: 1

siride
siride

Reputation: 209915

It's pretty simple, really. Subqueries can only return one row. That's what the error message tells you. The query you gave most likely returns more than one row (one for each class, in fact). Since you provide no output, I can only guess that this is true.

To fix it, you need to change that query to return one row. Perhaps you get rid of the GROUP BY, or perhaps you pick the largest (or smallest) sum and return that. It's up to you based on your business requirements.

Upvotes: 0

Related Questions