SUM DISTINCT multiple columns

[Joel Pearson]

I have a complicated problem. Well it's complicated because I want the query to run fast.

In the query below you see that I left join 2 tables billVendorRecItem and billVendorAdjustment. Both of these tables could have 0 to many rows. I am also grouping by the Column that I am joining both of these tables on.

For speeds sake I can't use subqueries, because this table already has about 15,000 rows.

What I am trying to achieve is to sum distinct values of the amount column, but I want the rows to be made distinct by their primary key bva.VendorAdjustmentID not by their value. So if I could do something like SUM(DISTINCT bva.VendorAdjustmentID VALUE bvri.Amount).
The other alternative I was thinking is if the left join will only join for the number of rows it has for the given table and goes back to null later. But I couldn't see anything fancy joins like that.

To explain more I mean for a given ItemVendorID I could have 3 rows from the billVendorRecItem table and 1 from the billVendorAdjustment table.

So that means I will get 2 rows for the given ItemVendorID. Say RecItem has 20, 20 and 10 and VendorAdjustment has just 3. Currently if I do a SUM(DISTINCT RecAmount) and SUM(DISTINCT AdjustAmount) I would get 30 and 3. But the RecAmount should add up to 50.

I tried using a UNION ALL as a subquery and then a group by but that was rather slow (1.8 seconds).

In the end I ended up adding the amount the primary key divided by pi (something which had enough decimal places that I could be fairly certain my data would never have. The Amount column is DECIMAL(11,2). Adding the Primary key divided by pi then made amount distinct by primary key. Which meant that my 20 and 20 would be not longer be counted as duplicates.
So then afterwards I had to subtract the sum(distinct) of the primary key/pi to get the correct result.

So anyway the query below works and is reasonably fast (.5 sec) but it seems a very roundabout sort of way to get to the solution. Can anyone shed any light on this situation and think of a better way of doing this, besides writing my own aggregate functions in C and installed them as a UDF function?


SELECT
ROUND(SUM(DISTINCT (bvri.Amount + (bvri.VendorRecID/PI()))) - SUM(DISTINCT (bvri.VendorRecID/PI())),2) AS TotalPaid,
ROUND(SUM(DISTINCT (bva.Amount + (bva.VendorAdjustmentID/PI()))) - SUM(DISTINCT (bva.VendorAdjustmentID/PI())),2) AS TotalAdjust
FROM billInvoiceItemVendor biiv
INNER JOIN billInvoiceItem bii ON bii.InvoiceItemID = biiv.InvoiceItemID
INNER JOIN billInvoice bi ON bi.InvoiceID = bii.InvoiceID
INNER JOIN tbcompany c ON c.companyid = bi.ClientID
LEFT JOIN billVendorRecItem bvri ON bvri.ItemVendorID = biiv.ItemVendorID
LEFT JOIN billVendorAdjustment bva ON bva.ItemVendorID = biiv.ItemVendorID
WHERE
biiv.VendorID = '1'
GROUP BY biiv.ItemVendorID
order by yearmonth, shortname asc