declare @counting table ([date] date, firstname char(10), name char(10), nationality char(2), mode char(3), origine char(1), ID char(2))
INSERT INTO @counting values
('2010-01-01','teste','teste1','fr','in','p','01'),
('2005-07-15','toto','tata','lb','out','L','02'),
('2012-03-01','teste','teste1','fr','in','P','01')


SELECT 
	ID, 
	COUNT(ID) MyCount
FROM
	@counting
GROUP BY ID
ORDER BY MyCount DESC

if object_id('tempdb..#Aggregate') is not null
	drop table #Aggregate
	
create table #Aggregate
(
	ID int identity,
	SomeValue varchar(50)
)

insert #Aggregate
select top 100 name from sys.objects

select ID, count(*)
from #Aggregate
group by ID
order by count(*)

select top 100 ID, 
count (1) AS cnt
from [database].[schema].[table]
group by id
order by cnt DESC;