How do I add a column that counts unique instances of another columns data

I am an end user that does not work with SQL everyday. However I am sometimes asked to add modify existing queries to provide new data.

Currently I have a query that calculates (counts) the occurrences of a column labeled (Qid) and displays the top 5 based on count. It was necessary to use a select statement within a select statement to do this (I had to get some help)

I am being asked if it is possible to add a column that counts the unique instances of column entries “Scan_Run_Date_Week” for each of those associated top 5 QID. The new column is designed to display how many unique weeks the correlating QID has been present. (I Refer to it as “Time on Track”)

Attached below is a sample set of the data being worked with. How would I modify my existing query to include this newly desired column (Time_On_Track)

Kindest regards,

Runatyr

Select 
       QID_Count,
       QID,
       Scan_Run_Date_Week,
       Title,
       Severity
From
        (
        SELECT count(QID) as QID_Count, 
               QID,
               Scan_Run_Date_Week, 
               Title,
               row_number() over(partition by Scan_Run_Date_Week order by count(QID) desc) as Ranking,
               Severity

        FROM dbo.RMO_Qualys_PROD 

        Where Severity = '5' and Scan_Run_Date_Week = '2021-06-06 00:00:00.000'


        Group By Scan_Run_Date_Week, Title, QID, Severity
        ) as Ranking
Where Ranking <=5

Answer

SQL Server doesn’t support count(distinct) as a window function, but there is a workaround using the sum of two dense_rank()s. So I think you want:

Select QID_Count, QID, Scan_Run_Date_Week, Title, Severity,
       num_scan_run_date_week
From (SELECT count(QID) as QID_Count, QID, Scan_Run_Date_Week,  Title,
             row_number() over(partition by Scan_Run_Date_Week order by count(QID) desc) as Ranking,
             Severity,
             (-1 + dense_rank() over (partition by qid order by scan_run_date_week asc) +
              dense_rank() over (partition by qid order by scan_run_date_week desc)
             ) as num_scan_run_date_week
      FROM dbo.RMO_Qualys_PROD 
      Where Severity = '5' and
            Scan_Run_Date_Week = '2021-06-06 00:00:00.000'
      Group By Scan_Run_Date_Week, Title, QID, Severity
     ) as Ranking
Where Ranking <= 5;