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?
I think you want something like the following:
The problem with your query is that
LEFT JOIN
s replicateInvoices_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
andInvoices_positions
tables and then join toInvoices
table.Fiddle Demo here