select distinct count

Question

hello,
how is it possible to get the distinct count?
please see screen-shot which shows the data and what I would like the result to show as
Thank you

Answer 1

DROP TABLE IF EXISTS #temp  
  
CREATE TABLE #temp  
(  
ID int,  
sec nvarchar(5)  
)  
  
insert into #Temp (ID,sec) VALUES (999,'XXX')  
insert into #Temp (ID,sec) VALUES (999,'XXX')  
insert into #Temp (ID,sec) VALUES (888,'XXX')  
insert into #Temp (ID,sec) VALUES (888,'XXX')  
insert into #Temp (ID,sec) VALUES (432,'XXX')  
insert into #Temp (ID,sec) VALUES (432,'XXX')  
insert into #Temp (ID,sec) VALUES (678,'XXX')  
insert into #Temp (ID,sec) VALUES (678,'XXX')  
  
  
SELECT	ID,  
		sec,  
		COUNT(distinct sec)   
FROM	#temp   
GROUP BY ID,  
		 sec  

Answer 2

Hi @arkiboys
It is a easy question.

SELECT ID ,MAX(sec),COUNT(*) FROM TABLE GROUP BY ID  

Best Regard，
Isabella