| Author |
Topic |
|
JAdauto
Posting Yak Master
160 Posts |
 Posted - 2004-04-08 : 02:43:21
|
| Everyone has helped me so much in the passed, I am hoping to find the same again. Our application goes live tomorrow and I am in crunch time. We found a problem that I am at a loss for. Surely there is a way to accomplish this... I need (if possible) one sql statement that sums one field into three different totals based on a field value but grouped by a different field.I'll give a brief and simple example/overview, but if you need me to make up the data structure and sample data, I'd be happy to.EntityChargesEntityIDEntityTypeIDChargeTypeENUMAmountChargeTypeENUM values can be 1 (Locker),2 (Yacht) or 3 (Other)SELECT EntityTypeID,Sum(Amount) as TotalChargesFROM EntityChargeGROUP BY EntityTypeIDThis gives me a total of all chargetypes by EntityType.Sample result set:1 35.002 52.50What I am needing is a total for locker, a total for yacht and a total for other all grouped by EntityType:Using the same example above, I would need the sample results to look like:1 10.00 0 25.002 10.00 10.00 32.50Again, I appreciate any advice in this desperate time. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-08 : 02:59:14
|
| SELECT EntityTypeID,Sum(Locker) as TotalLocker, Sum(Yacht) as Totalacht, Sum(Other) as TotalOtherFROM EntityChargeGROUP BY EntityTypeIDDuane. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 03:04:28
|
| SELECT EntityTypeID, SUM(CASE WHEN ChargeTypeENUM = 'Locker' THEN ISNULL(Amount,0) ELSE 0 END AS LockerTotal, SUM(CASE WHEN ChargeTypeENUM = 'Yacht' THEN ISNULL(Amount,0) ELSE 0 END AS YachtTotal, SUM(CASE WHEN ChargeTypeENUM = 'Other' THEN ISNULL(Amount,0) ELSE 0 END AS OtherFROM EntityChargeGROUP BY EntityTypeIDIs this what you are looking for?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-08 : 03:04:31
|
| Ooops miss read the question.Try This:SELECT EntityTypeID,Sum(CASE when Chargetype = 1 Then Amount else 0 end) as TotalLocker, Sum(CASE when Chargetype = 2 Then Amount else 0 end) as TotalYacht, Sum(CASE when Chargetype = 3 Then Amount else 0 end) as TotalOtherFROM EntityChargeGROUP BY EntityTypeIDDuane. |
|
|
|
JAdauto
Posting Yak Master
160 Posts |
Posted - 2004-04-08 : 03:37:48
|
| A million and one thanks. This is EXACTLY What I was looking for and the solution worked perfectly. I think I must have tried every combination except this one. I just cannot get a grasp on these Case Statements and the many uses they can provide. Thank you for the speedy replies and helping me to meet this deadline! |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-08 : 03:42:53
|
| I think you should go to bed now. (03:37) over there! wow!Duane. |
|
|
|
JAdauto
Posting Yak Master
160 Posts |
Posted - 2004-04-08 : 03:48:44
|
| I am able to go to bed now knowing this is ready to compile tomorrow AM. It is 2:45am.. and one of the many late nights I have worked in the last 6 months. But a well deserved vacaction in Cancun on MONDAY!!!! Again.. many thanks! |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-04-08 : 10:41:30
|
| That's what I love about development people....One has the optimism (determination) to plan a holiday 2 workings days after "go-live date"....."But a well deserved vacaction in Cancun on MONDAY!!!!"There wouldn't be a (un)numbered bank account (or in your case....luxury yacht) down there waiting for you, would there? |
|
|
|
JAdauto
Posting Yak Master
160 Posts |
Posted - 2004-04-08 : 11:19:17
|
Well, if both of my dev partners had not already had vacations, and if I had not already booked the flight before the live-date was set, then I might feel guilty... well... maybe I feel a little bit guilty now.. but I think that will go away once I am on the beach.  |
|
|
|
|
Complicated Sum