-3
Query:
Select Manager, Sum(category1),
(select Sum(category2) from iris where month1 = "March" and Org1 = "ABC" group by Manager),
Sum(category1) - (select Sum(category2) from iris where month = "March" and Org1 = "ABC" group by Manager) from iris where month1 = "April" and Org1 = "ABC"
group by manager
order by manager;

The above query throws and error : "Subquery returns more than one row", please help

M.Hemant
  • 2,345
  • 1
  • 9
  • 14
Dharmick777
  • 1
  • Does this answer your question? [Solution to "subquery returns more than 1 row" error](https://stackoverflow.com/questions/28171474/solution-to-subquery-returns-more-than-1-row-error) – Luuk Mar 26 '22 at 13:10

2 Answers2

0

You have not explained your DB structure involved in this question.

Definitely, subquery may return multiple rows for specified month and org1 columns. You may use a LIMIT number statement:

Select Manager, Sum(category1),
(select Sum(category2) from iris where month1 = "March" and Org1 = "ABC" LIMIT 1 group by Manager),
Sum(category1) - (select Sum(category2) from iris where month = "March" and Org1 = "ABC" LIMIT 1 group by Manager) from iris where month1 = "April" and Org1 = "ABC"
group by manager
order by manager;

Have a look: https://www.w3schools.com/sql/sql_top.asp

It may probably work. Also, I see that you have repeating subquery. Probably, it is worth to reorganize your query as stored procedure of MySQL, so you may keep value of Sum(category2) in a variable. It'll be faster to execute. Check this:

https://www.sqlshack.com/learn-mysql-the-basics-of-mysql-stored-procedures/ https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

Ivan P.
  • 832
  • 2
  • 9
  • 26
0

I think that this is what you are looking for

Select Manager, 
  Sum(case when month1 = "Avril" then category1 else 0 end) cat1_avr,
  Sum(case when month1 = "March" then category2 else 0 end) cat2_mar,
  Sum(case when month1 = "Avril" then category1 else 0 end) 
  - Sum(case when month1 = "March" then category2 else 0 end) diff
from iris 
where 
  (month1 = "April" or month1 = "March")
and Org1 = "ABC"
group by manager
order by manager;