SUM of rows with GROUP CONCAT and LEFT JOIN

Question

I got a question regarding MySQL.

I got the following tables:

Invoices
invoices_ordermapping
invoices_positions

Invoices:

id | userid | state
1, 10, 1

invoices_ordermapping

id | invoiceid | orderid
1, 1, Order12
2, 1, Order13

invoices_positions

id | invoiceid | value | text
1, 1, 21, Example
2, 1, 18.9, Example 2

Now I would like to get the assigned orders to the invoice (Order12, Order13) and the total sum (Sum of value).

This is the result I expect:

Invoiceid | Orders | Sum
1, Order12, Order13, 39.9

I'm using this MySQL-Query:

SELECT group_concat(DISTINCT orderid ORDER BY orderid SEPARATOR ", ") AS orderid, 
       SUM(ip.total) as value
FROM tbl_invoices as a
LEFT JOIN tbl_invoices_ordermapping as oi ON oi.invoiceid = a.id 
LEFT JOIN tbl_invoices_positions as ip on ip.invoiceid = oi.invoiceid          
GROUP BY oi.invoiceid
ORDER BY oi.invoiceid

Unfortunately I get this result:

Orderid: Order12, Order12, Order13, Order13
Value: 119.69999885559082

What am I doing wrong here?

Answer 1

I think you want something like the following:

SELECT a.id AS InvoiceId, Orders, `Sum`
FROM Invoices as a
LEFT JOIN (SELECT invoiceid,
                  GROUP_CONCAT(DISTINCT orderid 
                               ORDER BY orderid SEPARATOR ', ') AS Orders
           FROM Invoices_ordermapping 
           GROUP BY invoiceid ) AS oi ON oi.invoiceid = a.id
LEFT JOIN (SELECT invoiceid, SUM(`value`) as `Sum`
           FROM Invoices_positions
           GROUP BY invoiceid) AS ip ON ip.invoiceid = a.id
ORDER BY a.id

The problem with your query is that LEFT JOINs replicate Invoices_positions rows, and, as a result, SUM is calculated on multiple occurrences of the same rows of the table.

You need to perform aggregation first, for both Invoices_ordermapping and Invoices_positions tables and then join to Invoices table.

Fiddle Demo here