Using HAVING COUNT(column name) NOT working as expected

Question

I have the SQL statement, working in SQL Server 2016, below where I'm getting records from an INNER SELECT UNION ALL statement and in the OUTER SELECT I'm trying to extract those records that meet the HAVING COUNT code however, my code is bringing back no records.
Here are the records from my INNER SELECT statement with UNION ALL:

LocationSysID	LocationName	AgentNo	RepID	RepName	Count	RN
5	A-MAX	22810	17025	mel	339	1
43	ADV	21371	17025	mel	100	1
43	ADV	21371	17025	mel	0	2
94	ALPINE	21249	17025	mel	64	1
94	ALPINE	21249	17025	mel	0	2
126	AMS	21537	17025	mel	333	1
138	ADV	22812	17025	mel	1180	1

I am trying via the OUTER SELECT to get those records from the INNER select statement where the COUNT(LocationSysID) > 1. So that the 4 records where the LocationSysID is 43 and 94 would be returned.

Any help/direction would be appreciated. Thanks.

Here is my SQL code:

SELECT x.LocationSysID, x.LocationName, x.AgentNo, x.RepID, x.RepName, x.TotalCount, x.RN
FROM (
    SELECT q.LocationSysID, q.LocationName, q.AgentNo, q.RepID, q.RepName, q.QuoteCount as TotalCount, '1' as RN
    FROM #tempYesQuotes q 
    WHERE q.QuoteCount > 0
    --ORDER BY LocationSysID asc;

    UNION ALL

    SELECT p.LocationSysID, p.LocationName, p.AgentNo, p.RepID, p.RepName, p.PolicyCount as TotalCount, '2' as RN
    FROM #tempNoNewPolicies p
    WHERE p.PolicyCount = 0
    --ORDER BY LocationSysID asc, RN asc;
) x
GROUP BY LocationSysID, LocationName, AgentNo, RepID, RepName, TotalCount, RN
HAVING COUNT(LocationSysID) > 1  
ORDER BY LocationSysID asc, RN asc;

Answer 1

The reason you are seeing the results that you describe in the question is because you group by all these fields:

GROUP BY LocationSysID, LocationName, AgentNo, RepID, RepName, TotalCount, RN

The values in TotalCount and RN are not the same, so each group has one row. Hence COUNT() function never returns anything but 1.

So, if you simply change your GROUP BY clause and remove columns TotalCount, RN from it you'll get the results that you expect. But, with the query structure that you have you'll have to remove these columns from the final result set as well.

I'm guessing that you want to keep them. To achieve that I'd recommend to use windowed COUNT() OVER() function instead of a plain COUNT.

I am using CTEs here to make the query readable. You can inline them into one large query if you want. It doesn't affect the performance.

WITH
CTE_Union
AS
(
    SELECT q.LocationSysID, q.LocationName, q.AgentNo, q.RepID, q.RepName, q.QuoteCount as TotalCount, '1' as RN
    FROM #tempYesQuotes q 
    WHERE q.QuoteCount > 0
    --ORDER BY LocationSysID asc;

    UNION ALL

    SELECT p.LocationSysID, p.LocationName, p.AgentNo, p.RepID, p.RepName, p.PolicyCount as TotalCount, '2' as RN
    FROM #tempNoNewPolicies p
    WHERE p.PolicyCount = 0
    --ORDER BY LocationSysID asc, RN asc;
)
,CTE_RowCount
AS
(
    SELECT
        LocationSysID, LocationName, AgentNo, RepID, RepName, TotalCount, RN
        ,COUNT(*) OVER (PARTITION BY LocationSysID) AS LocationCount
    FROM
        CTE_Union
)
SELECT
    LocationSysID, LocationName, AgentNo, RepID, RepName, TotalCount, RN
FROM
    CTE_RowCount
WHERE
    LocationCount > 1
ORDER BY
    LocationSysID asc, RN asc
;