Source Table

ID TRAN_ID CODTER
1 2 99999
2 3 25
3 4 35
4 2 99999
5 2 99999
6 3 25
7 3 25

Result Table

ID TRAN_ID CODTER COUNT
1 2 99999 3
4 2 99999 3
5 2 99999 3
6 3 25 2
7 3 25 2

Relative Query


select o.TRAN_ID, oc.Count, o.id,o.CODTER
from DataArchive o
inner join (
SELECT TRAN_ID, COUNT(*) AS Count
FROM DataArchive
GROUP BY TRAN_ID
HAVING COUNT(*) > 1
) oc on o.TRAN_ID = oc.TRAN_ID
order by TRAN_ID ,Count,id

Advertisements