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