SQL Server - Select Rows in Derived Table that have count > 1 of value in one column

Question

Help please. I have the following Select query and want to select only the rows where the column A.Name repeats more than 1 time:

SELECT
    A.Payer,
    A.PaymentDate,
    A.Name
FROM 
    (SELECT
         T.InstitutionRoleXrefLongName AS 'Payer',
         T.PaymentDate AS 'PaymentDate',
         T.FullName AS 'Name'
     FROM 
         Transfer T
     UNION ALL
     SELECT
         T.InstitutionRoleXrefLongName AS 'Payer',
         T.PaymentDate AS 'PaymentDate',
         T.FullName AS 'Name'
     FROM 
         TransferClosed T) A
WHERE 
    PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'

D-Shih · Accepted Answer · 2018-04-27T22:50:56.717

using CTE and self join to get the count which greater than 1.

You can try this.

 ;with CTE AS (
    SELECT
        A.Payer,
        A.PaymentDate,
        A.Name
    FROM (
        SELECT
            T.InstitutionRoleXrefLongName AS 'Payer',
            T.PaymentDate AS 'PaymentDate',
            T.FullName AS 'Name'
        FROM Transfer T
    UNION ALL
        SELECT
            T.InstitutionRoleXrefLongName AS 'Payer',
            T.PaymentDate AS 'PaymentDate',
            T.FullName AS 'Name'
        FROM TransferClosed T
    ) A
    WHERE PaymentDate Between '20180101' AND '20180331 23:59:59'
)
select t2.*
from (
    SELECT name,count(1) totle 
    FROM CTE
    GROUP BY Name
) t1 inner join CTE t2 
ON t1.totle > 1 and t1.Name = t2.Name

The sqlfiddle CTE mock your result set

sqlfiddle:http://sqlfiddle.com/#!18/cc68f/9

score 0 · Answer 2 · answered Apr 27 '18 at 23:00

WITH Payments AS (
    SELECT
        A.Payer, A.PaymentDate, A.Name,
        COUNT(*) OVER (PARTITION BY A.Name) AS NameCount
    FROM (
        SELECT
            T.InstitutionRoleXrefLongName AS Payer,
            T.PaymentDate AS PaymentDate,
            T.FullName AS Name
        FROM Transfer T
        UNION ALL
        SELECT
            T.InstitutionRoleXrefLongName AS Payer,
            T.PaymentDate AS PaymentDate,
            T.FullName AS Name
        FROM TransferClosed T
    ) A
    WHERE PaymentDate Between '20180101' AND '20180331 23:59:59'
)
SELECT * FROM Payments WHERE Name_Count > 1;

You might find that the query performs better by duplicating that date filter in both sides of the union. Just a thought.

SQL Server - Select Rows in Derived Table that have count > 1 of value in one column

2 Answers2