CODEWITHSUNDEEP
mysqljoinview
CMartins
CMartins

Reputation: 3293

Why this view doesn't sum correctly?

I have created this View with Navicat but the sums are wrong. enter image description here

SELECT
planeamento_conf.id_Plano AS id_Plano,
planeamento_conf.plan_ConfEnc AS plan_ConfEnc,
planeamento_conf.plan_Modelo AS plan_Modelo,
planeamento_conf.plan_Total_Pecas AS plan_Total_Pecas,
planeamento_conf.plan_Min_Peca AS plan_Min_Peca,
planeamento_conf.plan_Confecao AS plan_Confecao,
planeamento_conf.plan_Cap_Prod AS plan_Cap_Prod,
planeamento_conf.plan_Horas_Dia AS plan_Horas_Dia,
planeamento_conf.plan_Fatura AS plan_Fatura,
confeccoes.Confeccao AS Confeccao,
Sum(saidas_conf.Pecas_entregues) AS TotalSaidas,
Sum(entradas_pa.Pecas_entregues) AS TotalEntradas
FROM
((planeamento_conf
JOIN confeccoes ON ((planeamento_conf.plan_Confecao = confeccoes.ID_Confeccao))
JOIN saidas_conf ON ((planeamento_conf.id_Plano = saidas_conf.Plano))
JOIN entradas_pa ON ((planeamento_conf.id_Plano = entradas_pa.Plano)))
GROUP BY
planeamento_conf.id_Plano

the fields I want to sum are saidas_conf.Pecas_entregues and entradas_pa.Pecas_entregues I have this id_Plano common to the 3 tables

Upvotes: 0

Views: 34

Answers (2)

RamGrg
RamGrg

Reputation: 297

I had used similarly by following in my case. For my case it had worked. I hope it might work for you too. 

SELECT
planeamento_conf.id_Plano AS id_Plano,
planeamento_conf.plan_ConfEnc AS plan_ConfEnc,
planeamento_conf.plan_Modelo AS plan_Modelo,
planeamento_conf.plan_Total_Pecas AS plan_Total_Pecas,
planeamento_conf.plan_Min_Peca AS plan_Min_Peca,
planeamento_conf.plan_Confecao AS plan_Confecao,
planeamento_conf.plan_Cap_Prod AS plan_Cap_Prod,
planeamento_conf.plan_Horas_Dia AS plan_Horas_Dia,
planeamento_conf.plan_Fatura AS plan_Fatura,
confeccoes.Confeccao AS Confeccao,
Sum(saidas_conf.Pecas_entregues) AS TotalSaidas,
Sum(entradas_pa.Pecas_entregues) AS TotalEntradas
FROM planeamento_conf
JOIN confeccoes ON planeamento_conf.plan_Confecao = confeccoes.ID_Confeccao
JOIN saidas_conf ON planeamento_conf.id_Plano = saidas_conf.Plano
JOIN entradas_pa ON planeamento_conf.id_Plano = entradas_pa.Plano
GROUP BY
planeamento_conf.id_Plano

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

Think you will have to do the SUMs in a couple of sub queries:-

SELECT
    planeamento_conf.id_Plano AS id_Plano,
    planeamento_conf.plan_ConfEnc AS plan_ConfEnc,
    planeamento_conf.plan_Modelo AS plan_Modelo,
    planeamento_conf.plan_Total_Pecas AS plan_Total_Pecas,
    planeamento_conf.plan_Min_Peca AS plan_Min_Peca,
    planeamento_conf.plan_Confecao AS plan_Confecao,
    planeamento_conf.plan_Cap_Prod AS plan_Cap_Prod,
    planeamento_conf.plan_Horas_Dia AS plan_Horas_Dia,
    planeamento_conf.plan_Fatura AS plan_Fatura,
    confeccoes.Confeccao AS Confeccao,
    sub_saidas_conf.TotalSaidas,
    sub_entradas_pa.TotalEntradas
FROM
planeamento_conf
JOIN confeccoes ON ((planeamento_conf.plan_Confecao = confeccoes.ID_Confeccao))
JOIN (SELECT Plano, SUM(Pecas_entregues) AS TotalSaidas FROM saidas_conf GROUP BY Plano) AS sub_saidas_conf ON ((planeamento_conf.id_Plano = sub_saidas_conf.Plano))
JOIN (SELECT Plano, SUM(Pecas_entregues) AS TotalEntradas FROM entradas_pa GROUP BY Plano) AS sub_entradas_pa ON ((planeamento_conf.id_Plano = sub_entradas_pa.Plano))
GROUP BY
planeamento_conf.id_Plano

The outer GROUP BY may now not be required (depends if there are duplicates on confeccoes, which you need to eliminate - in which case DISTINCT might be a better choice).

Upvotes: 1

Related Questions