SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Genreal Query Question


Genreal Query Question

Author
Message
John524
John524
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 85
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

































Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172518 Visits: 23044
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;



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
drew.allen
drew.allen
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71034 Visits: 18742
Luis Cazares - Wednesday, February 14, 2018 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;



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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
John524
John524
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 85
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
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)SSC Guru (172K reputation)

Group: General Forum Members
Points: 172518 Visits: 23044
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];



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
John524
John524
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 85
thanks a ton!
I can't wait to try these options out. I appreciate the help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search