Left Join - Duplicate SUM

[chris.olver]

Hi all,

I've got stuck and starting to wind myself up.. Got 2 tables. I have a invoice table which stores the total of the invoice and a invoice parameters table which breaks down the invoice (by the item and cost of that item).

 

I have recently added the parameter table and imported the data. I am trying to write a query which shows me the maths are correct after the import. Ie the invoice total in the invoice table matches a sum of all the parameters (which it should) and then group it by each client ID (which is stored in the invoice). This should show me all the monies linked to a client based upon a total of all invoices and a hopeful identical figure of all the parameters from the invoices added up.

 

Some invoices have one parameter and other invoices have multiple.

 

SELECT i.`ProfileID`,
SUM(i.ConvertPounds), 
SUM(ip.revenue),
IF(SUM(i.`ConvertPounds`) != SUM(ip.`revenue`), 'Error', '') As Whatsup,
GROUP_CONCAT(i.`InvoiceID`) As InvoiceIDs
FROM implementation.invoice i
LEFT JOIN implementation.invoiceparam ip ON ip.invoiceID=i.InvoiceID
WHERE i.`LicenseID` != 0
GROUP BY i.`ProfileID`
ORDER BY IF(SUM(i.`ConvertPounds`) != SUM(ip.`revenue`), 'Error', '') DESC;

 

What I am finding is that some invoices are duplicating in SUM(i.CovertPounds) if an invoice has multiple parameters in the invoiceparameter table. I need that to stop as its screwing the results. If anyone can make any logic of what i'm trying to do.. any ideas? The only idea I have is put a profileID in the invoice parameter table and do the left join based on that instead of doing it on the invoice table

[kickstart]

Hi

 

Think the problem is that the fields from implementation.invoice will be repeated for each row on implementation.invoiceparam hence will land up summed multiple times.

 

Think you need to sum up the invoiceparam fields and then join that to the invoice table.

 

However not quite sure how your tables hang together with the grouping on invoiceid and profileid, so the following is just a pointer rather than a full solution.

 

SELECT i.`ProfileID`,
SUM(i.ConvertPounds),
ip.revenueSUM,
IF(SUM(i.`ConvertPounds`) != ip,revenueSUM, 'Error', '') As Whatsup,
GROUP_CONCAT(i.`InvoiceID`) As InvoiceIDs
FROM invoice i
LEFT OUTER JOIN (SELECT invoiceID, SUM(revenue) AS revenueSum FROM invoiceparam GROUP BY invoiceID) ip
ON i.invoiceID = ip.invoiceID
ORDER BY IF(SUM(i.`ConvertPounds`) != ip.revenueSUM, 'Error', '') DESC;

 

If you can give a few example rows of how you group / join / sum on invoice and profile ids then I will try and make it a bit more useful.

 

All the best

 

Keith

[chris.olver]

Hi Keith,

Many thanks.

 

Nearly there. implementation.invoice -> SUM(ConvertPounds) now works correctly but am finding that when I group as profile ID (so should show all the total of customers invoices vs. the total of all the parameters on those invoices), the revenueSUM calculation only calculates the first invoice with the client and not multiple invoices.  The only work around I can think off is again inserting a profileID field into invoiceparam and do the maths from that or expanding the subquery in the left outer join to also have a join with the invoice table and do that calculation then...

 

Any ideas is greatly appreciated.

 

Regards,

-Chris