Using HAVING or WHERE on SUM Fields

Question

Problem

We have a Users table and a Addresses table.Each user can have multiple addresses. We have a bit field called IsDefault where a user can select their default address. This field currently isnt mandatory and possibly will be in the future, so I need to do some analysis. Now I want to validate the addresses to see:

• How many addresses a given user has.
• How many of those addresses (if they have more than 1 address) have the IsDefault flag set to a 1.

Basically I want to see how many of my users who have multiple addresses, have not switched on any of their addresses to be their default.

I have the following SQL query so far:

SELECT  AD.User_Id,
        COUNT(AD.User_Id) AS HowManyAddresses,
        SUM(
            CASE WHEN 
                AD.IsDefault IS NULL 
                OR
                AD.IsDefault = 0
            THEN
                1
            ELSE
                0
            END
        ) AS DefaultEmpty,

        SUM(
            CASE WHEN 
                AD.IsDefault = 1
            THEN
                1
            ELSE
                0
            END
        ) AS DefaultAddress

FROM dbo.Addresses AS AD
    JOIN dbo.Users AS U
    ON U.Id = AD.User_Id
GROUP BY AD.User_ID
ORDER BY AD.User_Id

The problem I have found is I want to check the values from the DefaultAddress and DefaultEmpty SELECT SUM fields, but I get the following error when trying to reference them using WHERE or HAVING:

Invalid column name 'DefaultEmpty'.

Is it not possible to reference SUM values for selection purposes?

Technology using:

1. SQL Server 2008
2. SQL Server Management Studio 2008

Answer 1

Actually you need to repeat the whole SUM clause with HAVING like this -

SELECT
    AD.User_Id
    ,COUNT(AD.User_Id) AS HowManyAddresses
    ,SUM(
    CASE
        WHEN
            AD.IsDefault IS NULL OR
            AD.IsDefault = 0 THEN 1
        ELSE 0
    END
    ) AS DefaultEmpty
    ,SUM(
    CASE
        WHEN
            AD.IsDefault = 1 THEN 1
        ELSE 0
    END
    ) AS DefaultAddress

FROM dbo.Addresses AS AD
JOIN dbo.Users AS U
    ON U.Id = AD.User_Id
GROUP BY AD.User_ID
HAVING SUM(
CASE
    WHEN
        AD.IsDefault IS NULL OR
        AD.IsDefault = 0 THEN 1
    ELSE 0
END
) = 0
ORDER BY AD.User_Id

OR

DECLARE @address TABLE(UserID INT,Address VARCHAR(100),IsDefault BIT);
INSERT INTO @address VALUES
 (1,'User 1 default',1)
,(2,'User 2 non default',0)
,(3,'User 3 non default',0)
,(3,'User 3 default',1)
,(4,'User 4 default',1)
,(4,'User 4 second default',1);

SELECT
    COUNT(*) OVER () AS HowManyAddresses
    ,ISNULL(def0.DefaultEmpty, 0) AS DefaultEmpty
    ,ISNULL(def1.DefaultAddress, 0) AS DefaultAddress
FROM (SELECT
        AD.Address
        ,COUNT(AD.UserID) OVER (PARTITION BY AD.UserID) AS DefaultEmpty
    FROM @address AS AD
    WHERE (AD.IsDefault = 0)) def0
FULL JOIN (SELECT
        AD.Address
        ,COUNT(AD.UserID) OVER (PARTITION BY AD.UserID) AS DefaultAddress
    FROM @address AS AD
    WHERE (AD.IsDefault = 1)) def1
    ON def0.Address = def1.Address

Answer 2

You can use CTE in following if you want to use alias names in WHERE clause:

WITH cte AS (
SELECT  AD.User_Id,
        COUNT(AD.User_Id) AS HowManyAddresses,
        SUM(
            CASE WHEN 
                AD.IsDefault IS NULL 
                OR
                AD.IsDefault = 0
            THEN
                1
            ELSE
                0
            END
        ) AS DefaultEmpty,

        SUM(
            CASE WHEN 
                AD.IsDefault = 1
            THEN
                1
            ELSE
                0
            END
        ) AS DefaultAddress

FROM dbo.Addresses AS AD
    JOIN dbo.Users AS U
    ON U.Id = AD.User_Id
GROUP BY AD.User_ID
ORDER BY AD.User_Id
)
SELECT * 
FROM cte
WHERE /* You can use alias names here... */
GROUP BY /* You can use alias names here... */
HAVING /* You can use alias names here... */