Get total number of unique data values, based on an ID, in its own column?

Question

How do I get the total number of unique CarNum's for each OrderID in its own column?

Desired Results:

Current Results:

Below is code that builds the results in the 'Current Results' image:

 IF OBJECT_ID('tempdb..#testTable') IS NOT NULL DROP TABLE #testTable

CREATE TABLE #testTable
(
      OrderID INT           
    , CarNum INT            
    , TimeOfDay VARCHAR(10) 
    , OrderNum  INT         
    , TotalCarNum INT       
)

INSERT INTO #testTable(OrderID, CarNum, TimeOfDay, OrderNum)
VALUES
    (1111111,2069, 'AM', 1)
   ,(1111111,2199, 'AM', 2)
   ,(1111111,2147, 'AM', 3)
   ,(1111111,2147, 'PM', 1)
   ,(1111111,5025, 'PM', 2)
   ,(1111111,2069, 'PM', 3)
   ,(2222222,5009, 'AM', 1)
   ,(2222222,6111, 'AM', 1)
   ,(2222222,7111, 'AM', 1)

SELECT  TT.OrderID
      , TT.CarNum
      , TT.TimeOfDay
      , TT.OrderNum
      , ROW_NUMBER() OVER (PARTITION BY TT.CarNum ORDER BY CarNum) AS TotalCarNum
      , COUNT(TT.CarNum) OVER (PARTITION BY TT.CarNum ORDER BY CarNum) AS TotalCarNum2
FROM    #testTable AS TT
ORDER BY TT.OrderID, TT.TimeOfDay, TT.OrderNum

Does this answer your question? [How to do a COUNT(DISTINCT) using window functions with a frame in SQL Server](https://stackoverflow.com/questions/63527035/how-to-do-a-countdistinct-using-window-functions-with-a-frame-in-sql-server) — Thom A, Mar 16 '22 at 13:13
Does this answer your question? [Window functions to count distinct records](https://stackoverflow.com/questions/13480880/window-functions-to-count-distinct-records) — Thom A, Mar 16 '22 at 13:14
Hi @Larnu, Gordan's answer is probably best, but I don't understand it. I've not heard of frames before. K4M's answer does have the explanation of dense rank. The second link doesn’t explain the dense rank like K4M’s answer. Thanks for pointing those out. I didn't know how to word it. Dourayd's answer works for windowing functions and the other answer with a subquery works as well. — JM1, Mar 16 '22 at 14:14

score 2 · Answer 1 · answered Mar 16 '22 at 13:18

you need Subquery to get your desired result

SELECT t.orderid,
       t1.carnum,
       t.timeofday,
       t.ordernum
FROM   #testtable T
       JOIN (SELECT orderid,
                    Count(DISTINCT carnum) CarNum
             FROM   #testtable
             GROUP  BY orderid) T1
         ON T.orderid = T1.orderid
ORDER  BY T.orderid,
          T.timeofday,
          T.ordernum

score 2 · Accepted Answer · answered Mar 16 '22 at 13:48

You can use dense_rank to mimic COUNT(DISTINCT) behavior

SELECT TT.OrderID
    ,TT.CarNum
    ,TT.TimeOfDay
    ,TT.OrderNum
    ,DENSE_RANK() OVER (
        PARTITION BY OrderID ORDER BY CarNum
        ) + DENSE_RANK() OVER (
        PARTITION BY OrderID ORDER BY CarNum DESC
        ) - 1 AS TotalCarNum
FROM #testTable AS TT
ORDER BY TT.OrderID
    ,TT.TimeOfDay
    ,TT.OrderNum

score 1 · Answer 3 · answered Mar 16 '22 at 15:11

You can use COUNT DISTINCT and a common table expression:

;WITH
CNT AS (
    SELECT OrderID, COUNT(DISTINCT CarNum) TotalCarNum
    FROM #testTable
    group by OrderID
)
SELECT  TT.OrderID
      , TT.CarNum
      , TT.TimeOfDay
      , TT.OrderNum
      , cnt.TotalCarNum
FROM    #testTable AS TT
join cnt on cnt.OrderID = tt.OrderID
ORDER BY TT.OrderID, TT.TimeOfDay, TT.OrderNum

Get total number of unique data values, based on an ID, in its own column?

3 Answers3