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 andifnull()
requires a Singleton.– Augusto Vasques
I think the
b.profit >= 0
is not the cause because he is inwhere
that returns acount
that will return only one value, but theifnull()
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 agroup by
, if there are differentmethod_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– Ricardo Pontual
The subquery has a "group by", why ?
– Motta
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
– Martins