Subtracting sub queries - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Subtracting sub queries

Author

Topic

edp33
Starting Member

9 Posts

Posted - 2008-09-10 : 11:16:03

I am trying to get a total from the following formula
Forecast=Total-Group-Operating-Project-Payroll

To obtain the individual parts of the forecast, Total, Group etc I use

SELECT CellValue As Total FROM Cells WHERE CategorySubCategoryID=18 AND MonthID=@MonthID

SELECT CellValue As Group FROM Cells WHERE CategorySubCategoryID=15 AND MonthID=@MonthID
etc

However, (I am probably being very thick here!) I can't for the live of me figure out how to do obtain total Forecast as I can't see a way to subtract each of the sub queries

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 12:01:54

[code]SELECT Total - [Group] - Operating - Project - Payroll AS Forecast,
Total,
[Group],
Operating,
Project,
Payroll
FROM (
SELECT SUM(CASE WHEN CategorySubCategoryID = 18 THEN 1 ELSE 0 END) AS [Total],
SUM(CASE WHEN CategorySubCategoryID = 15 THEN 1 ELSE 0 END) AS [Group],
SUM(CASE WHEN CategorySubCategoryID = xx THEN 1 ELSE 0 END) AS Operating,
SUM(CASE WHEN CategorySubCategoryID = yy THEN 1 ELSE 0 END) AS Project,
SUM(CASE WHEN CategorySubCategoryID = zz THEN 1 ELSE 0 END) AS Payroll
FROM Cells
WHERE CategorySubCategoryID IN (18, 15, xx, yy, zz)
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

edp33
Starting Member

9 Posts

Posted - 2008-09-10 : 12:09:25

Thank you so much :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 12:16:01

[code]SELECT Total - [Group] - Operating - Project - Payroll AS Forecast,
Total,
[Group],
Operating,
Project,
Payroll
FROM (
SELECT SUM(CASE WHEN CategorySubCategoryID = 18 THEN CellValue ELSE 0 END) AS [Total],
SUM(CASE WHEN CategorySubCategoryID = 15 THEN CellValue ELSE 0 END) AS [Group],
SUM(CASE WHEN CategorySubCategoryID = xx THEN CellValue ELSE 0 END) AS Operating,
SUM(CASE WHEN CategorySubCategoryID = yy THEN CellValue ELSE 0 END) AS Project,
SUM(CASE WHEN CategorySubCategoryID = zz THEN CellValue ELSE 0 END) AS Payroll
FROM Cells
WHERE CategorySubCategoryID IN (18, 15, xx, yy, zz)
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources