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:

SQL Server 2008
SQL Server Management Studio 2008

It is possible to use SUM values, but you cannot refer to column aliases in the where or having clauses, so you would have to retype the aggregate, e.g. `HAVING SUM(CASE WHEN AD.IsDefault IS NULL OR AD.IsDefault = 0 THEN 1 ELSE 0 END) > 1`. The `SELECT` clause is after the `WHERE/HAVING` clause with in the [Logical Processing Order](https://msdn.microsoft.com/en-us/library/ms189499.aspx) therefore when both are executed those columns aliases have not yet been defined. — GarethD, Jan 11 '16 at 12:09

Krishnraj Rana · Accepted Answer · 2016-01-11T13:15:33.960

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

Shnugo · Answer 2 · 2016-01-11T12:17:01.237

This will count - grouped by user and default, how many addresses there are:

DECLARE @user TABLE(ID INT, Name VARCHAR(100));
INSERT INTO @user VALUES
 (1,'user1') --will get a default
,(2,'user2') --no default
,(3,'user3') --both
,(4,'user4') --two defaults
,(5,'user5');--nothing

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);

EDIT: Better with PIVOT...

SELECT p.*
FROM
(
    SELECT u.ID,u.Name
          ,CASE WHEN a.IsDefault=1 THEN 'DEFAULT' ELSE 'NORMAL' END AS PivotColumn
          ,COUNT(a.UserID) AS CountPerUserAndDefault
    FROM @user AS u
    LEFT JOIN @address AS a ON u.ID=a.UserID
    GROUP BY u.ID,u.Name,a.IsDefault
) AS tbl
PIVOT
(
    SUM(CountPerUserAndDefault) FOR PivotColumn IN([DEFAULT],[NORMAL])
) AS p

The result:

Name    ID  DEFAULT NORMAL
user1   1   1       NULL
user2   2   NULL    1
user3   3   1       1
user4   4   2       NULL
user5   5   NULL    0

score 1 · Answer 3 · answered Jan 11 '16 at 12:12

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... */

score 0 · Answer 4 · edited Dec 28 '18 at 18:44

0

Put this after GROUPBY:

HAVING SUM(CAST(AD.IsDefault AS INT)) > 0

edited Dec 28 '18 at 18:44

garfbradaz

3,424
7
43
70

answered Jan 11 '16 at 12:23

Amit

75
7

Using HAVING or WHERE on SUM Fields

4 Answers4