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

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

CodePudding user response：

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

CodePudding user response：

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