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.
| Author |
Topic |
|
Aerathi
Starting Member
14 Posts |
 Posted - 2006-04-12 : 15:42:52
|
Hi I'm am having trouble working out in my head how to do multiple sums from data collected from the same field. For instance let's say I have the following rows:OrderID Amount SomeFlag 1 100 0 1 50 1 1 200 1 2 60 1 2 100 1 2 50 0 2 200 1 Ok basically what I need in this query is a total sum per order and also a total sum per order where SomeFlag = 1. This is a HUGELY stripped down example so I would prefer to stay away from subqueries if possible, because the query already joins to like 7 tables.Basically I need a way to combine the two queries below (based off the sample data above):SELECT SUM(Amount) FROM TestData GROUP BY OrderIDAnd:SELECT SUM(Amount), SomeFlag FROM TestData GROUP BY OrderID HAVING (SomeFlag = 1)That SomeFlag is also a trimmed version, it is about 3 table joins in and of itself, and it's really a mix of multiple things. If there's any way to do this that you know off the top of your head I would be appreciative, but I have an idea or two of what could work, but it makes the query so complex and sloppy :/Really when all is said and done, there will be four sums or variations of sums, based off different subsets of the same data. |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-04-12 : 16:29:54
|
You can sum like this:SUM(Amount) as totalSum,SUM(CASE WHEN SomeFlag = 1 THEN Amount ELSE 0 END) as sumFlag1note this is a very stripped down version of the complete solution  rockmoose |
 |
|
|
|
|
|
Multiple Sums