SQL Server: Counting the instance/unique value in an adjacent column-CodePudding

Please forgive me I am struggling for the correct term on what i'm trying to achieve.

I have a query result of responses and I'm trying to just have the count on the unique value in the adjacent column. COUNT(...) and COUNT(DISTINCT ...) are not giving me what I require.

date        total_responses   responseType   generationNumber
20-01-22    53                positive       125
20-01-22    7                 negative       125
15-01-22    73                positive       70
15-01-22    112               negative       70
07-01-22    126               positive       15
07-01-22    121               negative       15
03-01-22    74                positive       1
03-01-22    2                 negative       1

As you can hopefully see I aiming to count the unique values in generationNumber.

My SELECT statement:

SELECT MIN(a.[date]) AS 'date', COUNT(a.employee) AS 'total_responses', a.responseType , b.GenerationNumber

My desired outcome:

date        total_responses   responseType   generationNumber   unique_count
20-01-22    53                positive       125                4
20-01-22    7                 negative       125                4
15-01-22    73                positive       70                 3
15-01-22    112               negative       70                 3
07-01-22    126               positive       15                 2
07-01-22    121               negative       15                 2
03-01-22    74                positive       1                  1
03-01-22    2                 negative       1                  1

CodePudding user response：

EDIT - adding easier solution that I remembered but leaving the other as it does work.

with origQuery as(
        SELECT 
            MIN(a.[date]) AS 'date', 
            COUNT(a.employee) AS 'total_responses', 
            a.responseType , 
            b.GenerationNumber
        FROM YourTable
)
SELECT 
    *, 
    DENSE_RANK() OVER (ORDER BY generationNumber DESC) AS unique_count
FROM origQuery

Here is solution that should do it for you. Basicly, you need to use CTE to store results of your original query, get all distinct GenerationNumber values. Once you got that you can give them rownumbers and join them back to your original query based on GenerationNumber

with origQuery as(
    SELECT 
        MIN(a.[date]) AS 'date', 
        COUNT(a.employee) AS 'total_responses', 
        a.responseType , 
        b.GenerationNumber
    FROM YourTable
), 
distinctGenerationNumber as
(
    SELECT 
        DISTINCT generationNumber 
    FROM origQuery
), 
RowNumbers as (
    SELECT 
        generationNumber,
        row_number() over(order by generationNumber DESC) as unique_count
    FROM distinctGenerationNumber
)
SELECT 
    orig.*, 
    r.unique_count 
FROM origQuery orig JOIN RowNumbers r on orig.generationNumber = r.generationNumber