I have a table like
col1ID col2String Col3ID Col4String Col5Data
1 xxx 20 abc 14-09-2018
1 xxx 20 xyz 14-09-2018
2 xxx 30 abc 14-09-2018
2 xxx 30 abc 14-09-2018
I would like to add column which count how many different strings I have in col4String group by col1ID and col3ID.
So something like
COUNT(DISTINCT (Col4String)) over (partition by col1ID, col3ID)
but it doesn't work, I receive an error
Use of DISTINCT is not allowed with the OVER clause.
Msg 102, Level 15, State 1, Line 23.
I have more columns like col2String, col5Data but they shouldn´t be affected, so I can't use distinct at the beginning of SELECT
, and dense_rank()
also doen´t seems to work in my case.
Thank You for help.
Try this:
DECLARE @DataSource TABLE
(
[col1ID] INT
,[col2String] VARCHAR(12)
,[Col3ID] INT
,[Col4String] VARCHAR(12)
,[Col5Data] DATE
);
INSERT INTO @DataSource
VALUES (1, 'xxx', 20, 'abc', '2018-09-14')
,(1, 'xxx', 20, 'xyz', '2018-09-14')
,(2, 'xxx', 30, 'abc', '2018-09-14')
,(2, 'xxx', 30, 'abc', '2018-09-14');
SELECT *
,dense_rank() over (partition by col1ID, col3ID order by [Col4String]) + dense_rank() over (partition by col1ID, col3ID order by [Col4String] desc) - 1
FROM @DataSource
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With