Error Code: 1242. Subquery Returns more than 1 Row (but makes no sense)

Asked

Viewed 29 times

0

I need to know the registration number of a table grouped by method, and I also need to have the registration number of positives by positive methods, all this per user. I did something similar in another table worked but not in this one. (I am using mysql)

SELECT 
  busines.method_id, 
  m.name Metodo, 
  count(busines.id) Nro_busines, 
  ifnull(
    (
      select 
        (
          count(b.profit) nro_lucros 
          from 
            busines b 
            join busines on b.id = busines.id 
            and b.method_id = busines.method_id 
            and b.user_id = busines.user_id 
          where 
            b.user_id = "1598521" 
            and b.profit >= 0 
          group by 
            b.method_id
        ), 
        0
    ) as nro_lucros 
    FROM 
      busines 
      join methods m on m.id = busines.method_id 
      and m.user_id = busines.user_id 
    where 
      busines.user_id = "1598521" 
    group by 
      busines.method_id

Error Code: 1242. Subquery Returns more than 1 Row

Table key busines only id Key of ways only id

  • b.profit >= 0 generates the possibility of more than one result and ifnull() requires a Singleton.

  • I think the b.profit >= 0 is not the cause because he is in where that returns a count that will return only one value, but the ifnull() is that it may be getting in the way there as @Augustovasques commented, but what I think is the problem is the fact of using a group by, if there are different method_id will have more than one result, subquery must guarantee a single result, run it separately and remove the doubt, you will find the problem

  • The subquery has a "group by", why ?

  • I have a group by inside the subquery to count all the methods that are positive. Doesn’t it make sense? I’ve already removed ifnull etc , but I couldn’t

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.