Need the same sum in every row...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need the same sum in every row...

Author

Topic

gboulton
Starting Member

6 Posts

Posted - 2004-04-21 : 15:13:22

Ok, here's the deal...

I have a table of requests for donations, as follows (simplified for clarity):

Agency | Event | Amt_Requested | Amt_Given | EventDate

An example row might be:

Red Cross | Blood Drive | $5000 | $5000 | 2/2/04

Obviously, the same agency might be in the system more than once...say Red Cross has received money from us 5 times in the past, for example.

I need to produce a recordset that shows upcoming events, for certain event_date ranges, and includes a total of past contributions...for example:

Agency | Event | EventDate | Amt_Requested | Sum (amt_given) of all Previous Contributions to this Agency

Sooo....if we'd given $25,000 to Red Cross, $5000 for each of 5 events in the past, the result set for an upcoming event would look like:

Red Cross | Golf Tournament | 5/5/04 | $1000 | $25000

It would seem that a sum() with group By doesn't work here, since the moment I add all the fields to group by, it breaks down the sum by those fields...meaning I don't get a grand total, but rather a total just for events that fall in that group (which will, of course, wind up being = amt_given for that particular event)

Hope that makes sense.

If it helps to clarify things, look at it this way...I have someone who wants to see the $ amount requested for upcoming events (grouped by agency), AND see the total contributions we've given that agency in the past on EACH row.

Any ideas?

gboulton
Starting Member

6 Posts

Posted - 2004-04-21 : 15:36:02

Ok, for what it's worth, I've GOT the output I want with a god-awful hacked together cursor in an sp

There's GOT to be something prettier though...

hopkihc
Starting Member

8 Posts

Posted - 2004-04-21 : 20:37:14

I hope I understand your requirements. It seems you want to join on an subquery, like so:

SELECT Agency, Event, EventDate, AmtRequest, AgencyTotal
FROM Events e
INNER JOIN (SELECT Agency, SUM(AmtRequest) AS AgencyTotal FROM Events GROUP BY Agency) t
ON e.Agency = t.Agency

HTH!

John Hopkins
Augusta, GA

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-22 : 00:58:09

You don't need no stinkin' cursor here!

What you are looking for is a running total. Try using a correlated subquery to calculate that:


SELECT Agency, Event, EventDate, Amt_Requested,
 AgencyTotal = (SELECT SUM(Amt_Requested) FROM Events e1 WHERE e1.Agency = e.Agency AND e1.EventDate < e.EventDate)
FROM Events e

This will not handle ties, i.e. if you have two events on the same day, you will see the same number in the AgencyTotal column. You might want to include another column in the WHERE clause of the subquery, a primary works best. Try searching through these forums for "running total", you'll see plenty of ideas on this topic.

OS

gboulton
Starting Member

6 Posts

Posted - 2004-04-22 : 09:38:36

hopkihc,

Well DUHH. Why didn't _I_ think of that??? That's slick. Thanks! God I feel like an idiot now...

hopkihc
Starting Member

8 Posts

Posted - 2004-04-22 : 15:46:13

Glad it helped... although I see a problem in my query. It should have been Sum(AmtGIVEN) not AmtRequested... and mohdowais did raise the important consideration that if you want a running total as of the event date, the join condition would need to include EventDate. It seemed to me though that you wanted the grand total of all contributions (or that you would be querying upcoming events, not previous ones).

By the way, is it just me, or is doing the INNER JOIN preferable to doing AgencyTotal = (SELECT SUM... (as in mohdowais's example)?

I guess SQL Server optimizes it the same way... it just hurts my eyes to see subqueries statements nested within the SELECT clause.

John Hopkins
Augusta, GA

TKelley
Starting Member

1 Post

Posted - 2004-05-18 : 20:17:18

You might think about using Sql Server's " with roll up " command to total the query for more than one agency. It makes a nice report.
I just created a report in html with it.

Hey John, I live 5 miles from augusta.

Tim

Hephzibah Georgia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources