February 14, 2018 at 1:46 pm
Hello folks,
I've spent more time than I'd like to admit searching this topic, but I really feel like I'm not approaching this right and I have the nagging feeling I can't see the forrest for the trees. Hopfully one of you guys can steer me straight here.
I inherited two tables, let's call them Widget and WidgetDetails. I won't go too much into the weeds here, but I think you'll get the gist.
The columns in the Widget table are ID (pk), Group (int), and Invoice (float)
The columns in the WidgetDetail are ID (fk) and Payment (float)
So my query would be:
SELECT W.ID, W.Group, WD.Payment,W.Invoice
FROM Widget W JOIN WidgetDetail WD ON W.ID = WD.ID.
There can be many WidgetDetail records for each Widget record, here's one example of many of my various records.....let's say 4 payments of $25 for the one invoice of $100, so it would return... (ID#1 $25 $100) x 4
Anyway, here's my end goal: [Group], [TotalInvoice], and [TotalPayment]. So let's say there are 10 different groups so I'd have 10 records
I can't do SELECT Group, SUM(WD.PaymentAmount), SUM(W.InvoiceAmount) because it would sum the InvoiceAmount each time. Something tells me a subquery is the answer bouncing the one Invoice off the many payments using ID, and I have noodled with that, but to little avail.
I hope this is enough info... if anyone can help please do so. Either way, thanks for reading my rather clumsy post
February 14, 2018 at 1:55 pm
Don't aggregate the values on your Widget table, just group by them.
SELECT W.ID,
W.Group,
SUM( WD.Payment) AS Payment,
W.Invoice
FROM Widget W
JOIN WidgetDetail WD ON W.ID = WD.ID
GROUP BY W.ID, W.Group, W.Invoice;
February 14, 2018 at 2:04 pm
Luis Cazares - Wednesday, February 14, 2018 1:55 PMDon't aggregate the values on your Widget table, just group by them.
SELECT W.ID,
W.Group,
SUM( WD.Payment) AS Payment,
W.Invoice
FROM Widget W
JOIN WidgetDetail WD ON W.ID = WD.ID
GROUP BY W.ID, W.Group, W.Invoice;
Another option is to use MAX or MIN instead of SUM.
SELECT W.ID,
MAX(W.[Group]) AS [Group],
SUM( WD.Payment) AS Payment,
MAX(W.Invoice) AS Invoice
FROM Widget W
JOIN WidgetDetail WD ON W.ID = WD.ID
GROUP BY W.ID
Also, you shouldn't use reserved keywords as column names, but if you do, you should at least quote them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 14, 2018 at 2:10 pm
thanks for the quick reply Luis,
I would need to sum up the Invoices as well, but as to only sum them once per Widget.ID (as opposed to multiple times per WidgetDetails.ID) I'd also need to lose the ID from the select (yet keep the relationship) to return just the summary data by group. It's almost like I need to combine two different queries altogether.....just don't know how to go about it
thanks again
February 14, 2018 at 2:25 pm
Then you have multiple options:
Preaggregate using a derived table or CTE:
SELECT W.ID,
W.Group,
SUM( WD.Payment) AS TotalPayment,
SUM( W.Invoice) AS TotalInvoice
FROM Widget W
JOIN (SELECT iWD.ID,
SUM( iWD.Payment) AS Payment
FROM WidgetDetail AS iWD
GROUP BY iWD.ID) AS WD ON W.ID = WD.ID
GROUP BY W.[Group];
WITH AggregatedWidgetDetails AS(
SELECT iWD.ID,
SUM( iWD.Payment) AS Payment
FROM WidgetDetail AS iWD
GROUP BY iWD.ID
)
SELECT W.ID,
W.Group,
SUM( WD.Payment) AS TotalPayment,
SUM( W.Invoice) AS TotalInvoice
FROM Widget W
JOIN AggregatedWidgetDetails AS WD ON W.ID = WD.ID
GROUP BY W.[Group];
Use CROSS APPLY (this is usually slower).
SELECT W.ID,
W.Group,
SUM( WD.Payment) AS TotalPayment,
SUM( W.Invoice) AS TotalInvoice
FROM Widget W
CROSS APPLY (SELECT SUM( iWD.Payment) AS Payment
FROM WidgetDetail AS iWD
WHERE W.ID = iWD.ID) AS WD ON W.ID = WD.ID
GROUP BY W.[Group];
Or use a small hack that might have some problems with rounding.
SELECT W.Group,
SUM( WD.Payment) AS TotalPayment,
SUM( W.Invoice) / COUNT(*) AS TotalInvoice
FROM Widget W
JOIN WidgetDetail WD ON W.ID = WD.ID
GROUP BY W.[Group];
February 14, 2018 at 2:48 pm
thanks a ton!
I can't wait to try these options out. I appreciate the help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply