Distincted ids for grouped values

I want to count the distinct ids in each numb and store them in a column : Tried this:

WITH T AS(
SELECT 
  MAX(CASE WHEN LOGS like'CAR%' then REPLACE(LOGS,'CAR-','')end)as CAR,
  MAX(CASE WHEN LOGS like 'MOT%' then REPLACE(LOGS,'MOT-','')end)as MOTO,
  MAX(CASE WHEN LOGS like 'BICYCLE%' then REPLACE(LOGS,'BICYCLE-','')end)as BICYCLE,
  MAX(CASE WHEN LOGS like 'SHIP%' then REPLACE(LOGS,'SHIP-','')end)as SHIP,
  ID,
  ORIG,
  DATE_ID ,
  NUMB,
  STEPS 
from dbo.test
group by ORIG,DATE_ID,ID ,NUMB,STEPS
)
SELECT ID,ORIG,NUMB,STEPS,DATE_ID,CAR,MOTO,BICYCLE,SHIP,
  (SELECT COUNT(DISTINCT ID) FROM dbo.test tp WHERE ORIG= '4567') as COUNTER
from t
where ORIG= '4567'
  and NUMB in('1515','1921','2121')
GROUP BY ID,ORIGIN_URI,NUMB,STEPS,DATE_ID,CAR,MOTO,BICYCLE,SHIP

Receive this output:

+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
| ID |     ORIG     | NUMB | STEPS | DATE_ID  |  CAR  | MOTO | BICYCLE | SHIP | COUNTER |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
|  1 |   4567       |   1515 |     1 | 20201010 | HONDA | NULL | NULL    | NULL |       3 |
|  1 |   4567       |   1515 |     2 | 20201010 | HONDA | NULL | NULL    | NULL |       3 |
|  1 |   4567       |   1515 |     3 | 20201010 | HONDA | NULL | NULL    | NULL |       3 |
|  2 |   4567       |   1921 |     1 | 20201111 | NULL  | KTM  | NULL    | NULL |       3 |
|  3 |   4567       |   2121 |     1 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
|  3 |   4567       |   2121 |     2 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
|  3 |   4567       |   2121 |     3 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
|  3 |   4567       |   2121 |     4 | 20201231 | NULL  | NULL | NULL    | BOAT |       3 |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+

As you can see COUNTER columns has the count of distincted ids but for all NUMB

I want to output this:

+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
| ID |     ORIG     | NUMB | STEPS | DATE_ID  |  CAR  | MOTO | BICYCLE | SHIP | COUNTER |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+
|  1 |   4567       |   1515 |     1 | 20201010 | HONDA | NULL | NULL    | NULL |       2 |
|  1 |   4567       |   1515 |     2 | 20201010 | HONDA | NULL | NULL    | NULL |       2 |
|  2 |   4567       |   1515 |     1 | 20201010 | HONDA | NULL | NULL    | NULL |       2 |
|  2 |   4567       |   1921 |     1 | 20201111 | NULL  | KTM  | NULL    | NULL |       1 |
|  3 |   4567       |   2121 |     1 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
|  3 |   4567       |   2121 |     2 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
|  3 |   4567       |   2121 |     3 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
|  1 |   4567       |   2121 |     1 | 20201231 | NULL  | NULL | NULL    | BOAT |       2 |
+----+--------------+--------+-------+----------+-------+------+---------+------+---------+

1515 has 2 ids
1921 has 1 id
2121 has 2 ids

I tried also to place a GROUP BY NUMB inside (SELECT COUNT(DISTINCT ID) FROM dbo.test tp WHERE ORIG= '4567') but didn’t work.

Answer

What you seem to want is:

count(distinct steps) over (partition by orig, numb)

Alas, SQL Server doesn’t support count(distinct) with window functions.

Happily, there is an easy workaround (which begs the question as to why the above syntax is not supported):

(dense_rank() over (partition by orig, numb order by steps asc) +
 dense_rank() over (partition by orig, numb order by steps desc) - 1
) as counter

Advertisement

Answer