Mysql sum query returning incorrect result when using multiple joins

Question

My query is returning incorrect sum of 2 columns from 2 sub tables, i searched over google and also looked the suggestion on stackoverflow but they never worked.

   si_invoices
   -----------------------------
   id, date
   1, 2014-05-07


   si_invoice_items
   -----------------------------
   id, invoice_id, date , total
   1, 100, 2014-05-07, 200
   2, 100, 2014-05-07, 200

   si_payment
   -----------------------------
   id, ac_inv_id, date , payment
   1, 100, 2014-05-07, 100
   2, 100, 2014-05-07, 200

   SELECT  SI.*,SUM(SII.total) as total,SUM(SIP.payment) as payment FROM 
        (SELECT * FROM si_invoices GROUP BY si_invoices.id) AS SI
     LEFT JOIN si_invoice_items SII ON SII.invoice_id = SI.id
     LEFT JOIN si_payment SIP ON SIP.ac_inv_id = SII.invoice_id
   GROUP BY SI.id

It should return 400 sum for field 'total' in sql but it return 800 and same as for 'payment'. Can you please point out what is the error in my query. Please help, appreciated.

Thanks M.S

I already explained desired result. Please go through the question. It should return 400 as sum but it is actually returning 800. — Mohit Singh
– Mohit Singh, Commented Apr 26, 2017 at 5:55
1.SELECT * FROM si_invoices GROUP BY si_invoices.id must be giving syntax error. Group By has only one column and you are trying to fetch more than one col. — Kapil
– Kapil, Commented Apr 26, 2017 at 6:16
SII.invoice_id = SI.id .It should be returning you null values. There is no matching data in their respective tables.So, final result must be Null. How are you getting any output (worng/right) with such syntax errors and null data mapping. — Kapil
– Kapil, Commented Apr 26, 2017 at 6:19
Instead of placing the expected result value.It would help if you mention the desired output you are trying to achieve.Is it just the sum of total and payment that you want to get for all the ids available in si_invoices. — Kapil
– Kapil, Commented Apr 26, 2017 at 6:22

dev8080 · Accepted Answer · 2017-04-26 06:26:04Z

3

Use the totals directly because your joins are probably creating more rows in combination that you want.

Try the following:

SELECT id, MAX(Total) as FinalTotal ,MAX(Payment) as FinalPayment
FROM si_invoices a 
    left join 
    (select invoice_id, sum(total) as Total from si_invoice_items group by invoice_id) b 
    on a.id = b.invoice_id
    left join
    (select ac_inv_id, sum(payment) as Payment from si_payment group by ac_inv_id) c 
    on c.ac_inv_id = a.id 
group by id

or if id is unique:

    SELECT *
FROM si_invoices a 
    left join 
    (select invoice_id, sum(total) as Total from si_invoice_items group by invoice_id) b 
    on a.id = b.invoice_id
    left join
    (select ac_inv_id, sum(payment) as Payment from si_payment group by ac_inv_id) c 
    on c.ac_inv_id = a.id

answered Apr 26, 2017 at 6:26

dev8080

4,0051 gold badge15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohit Singh Over a year ago

Thanks for the answer, your second query worked perfectly.

Vijunav Vastivch · Accepted Answer · 2017-04-26 06:05:20Z

0

Details Result:

select a.invoice,ac_inv_id,sum(a.total),sum(b.payment) from
(select 1 as id, 100 as invoice, '2014-05-07' as date, 200 as total union all
select  2, 100, '2014-05-07', 200) as a
left join
(select 1 as id, 100 as ac_inv_id, '2014-05-07' as date, 100 as payment union all
select 2, 100, 2014-05-07, 200) as b
on a.id = b.id

final query:

select a.invoice,ac_inv_id,sum(a.total),sum(b.payment) from
(select * from si_invoice_items) as a
left join
(select * from si_payment) as b
on a.id = b.id

result:

100 100 400 300

answered Apr 26, 2017 at 6:05

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Comments

Mahendra Kushwaha · Accepted Answer · 2022-12-07 12:48:50Z

0

SELECT projects.* , sum( project_tasks.ts_amount ) as cost_spent , sum( work_entries.w_amount ) as actual_amount FROM projects LEFT JOIN project_costs on project_costs.proj_id = projects.proj_id LEFT JOIN project_tasks on project_costs.cost_id = project_tasks.cost_id LEFT JOIN work_entries on project_costs.cost_id = work_entries.cost_id and work_entries.w_status = 'Approved' group by projects.proj_id;

edited Dec 7, 2022 at 12:48

answered Dec 7, 2022 at 12:46

Mahendra Kushwaha

12 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Mysql sum query returning incorrect result when using multiple joins

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related