How to fix aggregate in where clause?

Question

Msg 147, Level 15, State 1, Procedure vw_OverBudget, Line 10 [Batch Start Line 59]
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Code:

CREATE VIEW vw_OverBudget 
AS
    SELECT
        p.projectName,
        SUM(a.costtodate) AS sumActivityCosts,
        p.fundedbudget
    FROM
        Project AS p
    FULL OUTER JOIN
        Activity a ON p.projectid = a.projectid  
    WHERE 
        a.activityId IS NULL
        AND p.projectid IS NOT NULL
        AND SUM(a.costtodate) > p.fundedbudget
    GROUP BY
        p.projectID

Answer 1

A full outer join is not needed for this query. For your logic, a left join would seem to be correct:

Select p.projectName, sum(a.costtodate) AS sumActivityCosts,
       p.fundedbudget
From Project p left join
     Activity a 
     on p.projectid = a.projectid  
where a.activityId is null and p.projectid is not null 
Group By p.projectName, p.fundedbudget
having sum(a.costtodate) > p.fundedbudget;

However, this doesn't make sense to me. If a.activityId is null, then the most likely reason would be no matches. So, I'm pretty sure you just want an inner join with no where clause:

Select p.projectName, sum(a.costtodate) AS sumActivityCosts,
       p.fundedbudget
From Project p inner join
     Activity a 
     on p.projectid = a.projectid
Group By p.projectName, p.fundedbudget
having sum(a.costtodate) > p.fundedbudget;

Answer 2

SQL SERVER and maybe applicable to other database

missing field under group by

Group By
    p.projectID
,p.fundedbudget  --- to add

and to add

having sum >

remove sum at the where clause

the updated will be

    Create view vw_OverBudget AS
    Select 
        p.projectName,
        sum(a.costtodate) AS sumActivityCosts,
        p.fundedbudget
    From
        Project AS p
        full outer join Activity a ON p.projectid = a.projectid  
    WHERE 
        a.activityId is null and p.projectid is not null
    Group By
        p.projectID, p.fundedbudget
having SUM(a.costtodate) > p.fundedbudget
    go