Complicated Summation Query Help
-
Hello, I have two columns, a category and a value. Below is the data:
Category
Total
Devices
38111
Plant
6063
Improvements
2266
I want to add two more columns: Zero and Sum So the columns would be Category, Zero, Total, Sum Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total? The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time. JM I would be forever indebt to you if you could help out. Thanks!
-
Hello, I have two columns, a category and a value. Below is the data:
Category
Total
Devices
38111
Plant
6063
Improvements
2266
I want to add two more columns: Zero and Sum So the columns would be Category, Zero, Total, Sum Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total? The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time. JM I would be forever indebt to you if you could help out. Thanks!
After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table. The TEMP table would be something like this:
Category Zero Total Sum
Devices 0 38111 38111
Plan 38111 6063 44147
Improvements 44174 2266 4640Use the following code snipet to get you going with the logic ...
DECLARE myCURSOR CURSOR FOR
SELECT CATEGORY, TOTAL
FROM myTABLEOPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTALSET @PBAL = 0.0
WHILE(@@FETCH_STATUS=0) BEGIN
SET @GTOTAL = @PBAL + @XTOTAL
SET @MYSQL = 'INSERT INTO #MYTEMP'
SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
SET @MYSQL = @MYSQL + @GTOTAL + ')'PRINT @MYSQL
EXEC(@MYSQL)
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
END
CLOSE myCURSOR
DEALLOCATE myCURSORGO
Hope this helps you !:thumbsup:
-
After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table. The TEMP table would be something like this:
Category Zero Total Sum
Devices 0 38111 38111
Plan 38111 6063 44147
Improvements 44174 2266 4640Use the following code snipet to get you going with the logic ...
DECLARE myCURSOR CURSOR FOR
SELECT CATEGORY, TOTAL
FROM myTABLEOPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTALSET @PBAL = 0.0
WHILE(@@FETCH_STATUS=0) BEGIN
SET @GTOTAL = @PBAL + @XTOTAL
SET @MYSQL = 'INSERT INTO #MYTEMP'
SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
SET @MYSQL = @MYSQL + @GTOTAL + ')'PRINT @MYSQL
EXEC(@MYSQL)
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
END
CLOSE myCURSOR
DEALLOCATE myCURSORGO
Hope this helps you !:thumbsup: