Select project.[id] "ID", companyname.[name]"Company", project.projectname "ProjName", project.startdate "StartDate"
,SUM(BudgetTotal + AddTotal + StockTotal) "TotalBudget"
(SELECT ISNULL(SUM(budget.budget),0) AS BudgetTotal 
FROM budget WHERE budget.projectID = project.[id])
(SELECT ISNULL(SUM(budget_additionals.budget),0) AS AddTotal 
FROM budget_additionals WHERE budget_additionals.projectID = project.[id])
(SELECT ISNULL(SUM(project_stock.saleprice),0) AS StockTotal 
FROM project_stock WHERE project_stock.projectID = project.[id])

From project, companyname

WHERE project.companyid = companyname.[id]
AND (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY "Company"

Select project.[id] "ID"
,companyname.[name]"Company"
,project.projectname "ProjName"
,project.startdate "StartDate"
,SUM((SELECT ISNULL(SUM(budget.budget),0)FROM budget WHERE budget.projectID = project.[id]) + 
(SELECT ISNULL(SUM(budget_additionals.budget),0)FROM budget_additionals WHERE budget_additionals.projectID = project.[id]) + 
(SELECT ISNULL(SUM(project_stock.saleprice),0)FROM project_stock WHERE project_stock.projectID = project.[id])
) "Budget"

From project, companyname

WHERE project.companyid = companyname.[id]
AND (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY "Company"

quote:

---

Originally posted by GlynD02

I am trying to get the SUM of the values from a subquery and I just can't wrap my head around the subquery thing! Can anyone please point out the error of my ways?

I have tried 2 slightly different ones:

Select project.[id] "ID", companyname.[name]"Company", project.projectname "ProjName", project.startdate "StartDate"
,SUM(BudgetTotal + AddTotal + StockTotal) "TotalBudget"
(SELECT ISNULL(SUM(budget.budget),0) AS BudgetTotal 
FROM budget WHERE budget.projectID = project.[id])
(SELECT ISNULL(SUM(budget_additionals.budget),0) AS AddTotal 
FROM budget_additionals WHERE budget_additionals.projectID = project.[id])
(SELECT ISNULL(SUM(project_stock.saleprice),0) AS StockTotal 
FROM project_stock WHERE project_stock.projectID = project.[id])

From project, companyname

WHERE project.companyid = companyname.[id]
AND (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY "Company"

This gives me an "Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'From'" error...

The next one i tried looked like this:

Select project.[id] "ID"
,companyname.[name]"Company"
,project.projectname "ProjName"
,project.startdate "StartDate"
,SUM((SELECT ISNULL(SUM(budget.budget),0)FROM budget WHERE budget.projectID = project.[id]) + 
(SELECT ISNULL(SUM(budget_additionals.budget),0)FROM budget_additionals WHERE budget_additionals.projectID = project.[id]) + 
(SELECT ISNULL(SUM(project_stock.saleprice),0)FROM project_stock WHERE project_stock.projectID = project.[id])
) "Budget"

From project, companyname

WHERE project.companyid = companyname.[id]
AND (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY "Company"

And I get "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

Cheers in advance :-)
Glyn

---

Select project.[id] AS ID, 
companyname.[name] AS Company,
project.projectname AS ProjName,
project.startdate AS StartDate,
SUM(BudgetTotal + AddTotal + StockTotal) AS TotalBudget,
(SELECT SUM(CASE WHEN budget.budget IS NULL THEN 0 ELSE budget.budget END) 
FROM budget WHERE budget.projectID = project.[id]) AS BudgetTotal, 
(SELECT SUM(CASE WHEN budget_additionals.budget IS NULL THEN 0 ELSE budget_additionals.budget END) 
FROM budget_additionals WHERE budget_additionals.projectID = project.[id])AS AddTotal, 
(SELECT SUM(CASE WHEN project_stock.saleprice IS NULL THEN 0 ELSE project_stock.saleprice END) 
FROM project_stock WHERE project_stock.projectID = project.[id])
AS StockTotal 
From project
INNER JOIN companyname
ON project.companyid = companyname.[id]
WHERE (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY Company