Partition by used to count records (to find dupes)

  • I'm getting an error in the following query, can anyone help with? Of course ConcatCount is not allowed in the last two rows:

    SELECT [DOMAIN],[DATE], [TOTAL TIME], COUNT([AGENT NAME] + '    ' + [TOTAL TIME]) 
    Over (PARTITION BY([AGENT NAME] + ' ' + [TOTAL TIME])) AS ConcatCount

    FROM [a2hr].[dbo].[stgOneConvoHrs] where date > '2021-04-18'
    group by ConcatCount
    having ConcatCount > 1

     

  • First step would be to tell us the error as the error likely says how to fix it.

    My GUESS is that you are getting a "invalid column name" error which makes sense as you are trying to group by a column ALAIS, not a column.

    So, to do what you want, you are going to need to either put it into a CTE OR put it into a nested select.  Something along the lines of:

    WITH cte AS (
    SELECT [DOMAIN],[DATE], [TOTAL TIME], COUNT([AGENT NAME] + ' ' + [TOTAL TIME])
    Over (PARTITION BY([AGENT NAME] + ' ' + [TOTAL TIME])) AS ConcatCount

    FROM [a2hr].[dbo].[stgOneConvoHrs] where date > '2021-04-18'
    )
    SELECT *
    FROM cte
    group by ConcatCount
    having ConcatCount > 1

     

  • Okay this is confusing. You're using both a windowing function and GROUP BY.  I don't think that's right. If all you're doing is looking for duplicates, the simple pattern is like this:

    USE tempdb;
    go

    SELECT *
    FROM
    (
    SELECT Subjects.Person
    ,Subjects.Subj
    ,rn = ROW_NUMBER() OVER (PARTITION BY Subjects.Person, Subjects.Subj
    ORDER BY Subjects.Person, Subjects.Subj)
    FROM (VALUES ('John','Math'),('John','History'),('John','Math'),
    ('Fred','Math'),('Fred','History'),('Fred','Biology')) Subjects(Person,Subj)
    ) dupes
    WHERE dupes.rn > 1;

    The combination of Person and Total Time is strange. What are you trying to accomplish?

  • I'm getting this error from the CTE solution (which is on the right track I think): Column 'cte.DOMAIN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

     

  • This idea worked... and is simple... thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply