Left join sum error: how to solve this error?

I'm trying to run the query below, but the Group2 column is adding the values without grouping by the ORCAMENTO field. Can you help me:

select o.ORCAMENTO as "AP",o.VALOR as "Valor",
    IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0) AS "Faturamento",
    IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0) / o.VALOR as "Faturado",
    (o.VALOR - (IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0))) as "Pendente",
    (select SUM(ox.valor) 
        from orc01x as ox 
        left join orc01 as o on o.ORCAMENTO = ox.orcamento 
        where ox.tr =2) as "Grupo2",
    group_concat(distinct f.DOCUMENTO) as "Nota",
    group_concat(distinct f.SITUACAO) as "Status"
from fat02 as f 
left join orc01 as o on o.ORCAMENTO = substring(f.REFERENCIA,4,5)
join fat02x as fx on fx.FATURA = f.DOCUMENTO and substring(fx.REFERENCIA,4,5) = o.ORCAMENTO
where year(o.DATA_APROV) = '2024'
    and f.DOCUMENTO NOT IN (
        SELECT f1.DOCUMENTO 
        FROM fat02 AS f1 
        WHERE f1.SITUACAO = 'C'
    )
group by o.ORCAMENTO

I tried to do a separate left join, but that didn't work either.

Solution

You have no grouping in the Grupo2 subquery, and it's not a correlated subquery. So it's summing all rows in the join of the two tables, not grouping them by ORCAMENTO.

Instead of putting the subquery in the SELECT list, you should join with a grouped subquery.

select o.ORCAMENTO as "AP",o.VALOR as "Valor",
    IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0) AS "Faturamento",
    IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0) / o.VALOR as "Faturado",
    (o.VALOR - (IFNULL(SUM(fx.VALOR1), 0) + IFNULL(SUM(fx.VALOR2), 0))) as "Pendente",
    o2.Grupo2,
    group_concat(distinct f.DOCUMENTO) as "Nota",
    group_concat(distinct f.SITUACAO) as "Status"
from fat02 as f 
left join orc01 as o on o.ORCAMENTO = substring(f.REFERENCIA,4,5)
join fat02x as fx on fx.FATURA = f.DOCUMENTO and substring(fx.REFERENCIA,4,5) = o.ORCAMENTO
left join (
    select o.ORCAMENTO, SUM(ox.valor) AS Grupo2
    from orc01x as ox 
    left join orc01 as o on o.ORCAMENTO = ox.orcamento 
    where ox.tr =2
    GROUP BY o.ORCAMENTO
) AS o2 ON o.ORCAMENTO = o2.ORCAMENTO
where year(o.DATA_APROV) = '2024'
    and f.DOCUMENTO NOT IN (
        SELECT f1.DOCUMENTO 
        FROM fat02 AS f1 
        WHERE f1.SITUACAO = 'C'
    )
group by o.ORCAMENTO