How do you properly query the result of a complex join statement in SQL?

Question

New to advanced SQL!

I'm trying to write a query that returns the COUNT(*) and SUM of the resulting columns from this query:

DECLARE @Id INT = 1000;

SELECT 
    *,
    CASE
        WHEN Id1 >= 6 THEN 1
        ELSE 0
    END AS Tier1,
    CASE
        WHEN Id1 >= 4 THEN 1
        ELSE 0
    END AS Tier2,
    CASE
        WHEN Id1 >= 2 THEN 1
        ELSE 0
    END AS Tier3
    FROM (
        SELECT
            Org.OrgID,
            App.AppID,
            App.FirstName,
            App.LastName,
            MAX(AppSubmitU_Level.Id1) AS Id1
        FROM Org
        INNER JOIN AppEmployment
            ON AppEmployment.OrgID = Org.OrgID
        INNER JOIN App
            ON App.AppID = AppEmployment.AppID
        INNER JOIN AppSubmit
            ON App.AppID = AppSubmit.AppID
        INNER JOIN AppSubmitU_Level
            ON AppSubmit.LevelID = AppSubmitU_Level.Id1
        INNER JOIN AppEmpU_VerifyStatus
            ON AppEmpU_VerifyStatus.VerifyStatusID = AppEmployment.VerifyStatusID
        WHERE AppSubmitU_Level.SubmitTypeID = 1 -- Career
        AND AppEmpU_VerifyStatus.StatusIsVerified = 1
        AND AppSubmit.[ExpireDate] IS NOT NULL
        AND AppSubmit.[ExpireDate] > GETDATE()
        AND Org.OrgID = @Id
        GROUP BY
            Org.OrgID,
            App.AppID,
            App.FirstName,
            App.LastName
    ) employees

I've tried to do so by moving the @Id outside the original query, and adding a SELECT(*), SUM, and SUM to the top, like so:

DECLARE @OrgID INT = 1000;

SELECT COUNT(*), SUM(employees.Tier1), SUM(employees.Tier2), SUM(employees.Tier3) 
FROM
    (SELECT *,
        ...
    ) AS employees
);

When I run the query, however, I'm getting the errors:

The multi-part identifier employees.Tier1 could not be bound

The same errors appear for the other identifiers in my SUM statements.

I'm assuming this has to do with the fact that the Tier1, Tier2, and Tier3 columns are being returned by the inner join query in my FROM(), and aren't values set by the existing tables that I'm querying. But I can't figure out how to rewrite it to initialize properly.

Thanks in advance for the help!

Answer 1

This is a scope problem: employees is defined in the subquery only, it is not available in the outer scope. You basically want to alias the outer query:

DECLARE @OrgID INT = 1000;
SELECT COUNT(*), SUM(employees.Tier1) TotalTier1, SUM(employees.Tier2) TotalTier2, SUM(employees.Tier3) TotalTier3
FROM (
    SELECT *,
    ...
    ) AS employees
) AS employees;
--^ here

Note that I added column aliases to the outer query, which is a good practice in SQL.

It might be easier to understand what is going on if you use another alias for the outer query:

SELECT COUNT(*), SUM(e.Tier1), SUM(e.Tier2), SUM(e.Tier3) 
FROM (
    SELECT *,
    ...
    ) AS employees
) AS e;

Note that you don't actually need to qualify the column names in the outer query, since column names are unambigous anyway.

And finally: you don't actually need a subquery. You could write the query as:

SELECT 
    SUM(CASE WHEN Id1 >= 6 THEN 1 ELSE 0 END) AS TotalTier1,
    SUM(CASE WHEN Id1 >= 4 THEN 1 ELSE 0 END) AS TotalTier2,
    SUM(CASE WHEN Id1 >= 2 THEN 1 ELSE 0 END) AS TotalTier3
FROM (
    SELECT
        Org.OrgID,
        App.AppID,
        App.FirstName,
        App.LastName,
        MAX(AppSubmitU_Level.Id1) AS Id1
    FROM Org
    INNER JOIN AppEmployment
        ON AppEmployment.OrgID = Org.OrgID
    INNER JOIN App
        ON App.AppID = AppEmployment.AppID
    INNER JOIN AppSubmit
        ON App.AppID = AppSubmit.AppID
    INNER JOIN AppSubmitU_Level
        ON AppSubmit.LevelID = AppSubmitU_Level.Id1
    INNER JOIN AppEmpU_VerifyStatus
        ON AppEmpU_VerifyStatus.VerifyStatusID = AppEmployment.VerifyStatusID
    WHERE AppSubmitU_Level.SubmitTypeID = 1 -- Career
    AND AppEmpU_VerifyStatus.StatusIsVerified = 1
    AND AppSubmit.[ExpireDate] IS NOT NULL
    AND AppSubmit.[ExpireDate] > GETDATE()
    AND Org.OrgID = @Id
    GROUP BY
        Org.OrgID,
        App.AppID,
        App.FirstName,
        App.LastName
) employees