Multiple group by queries in MYSQL-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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;