Removing Duplicates in SQL

Hi all,

I have a requirement to remove duplicates from a set of data as per the example below, can anyone advise how I can do this using just SQL:

Raw Data
Col1 Col2 Col3         Col4
1     100 color:yellow    Y
2      64 rtdiagstripe    Y
3      64                 Y
4      64                 Y
5      64 rtstripe        N
6      64 rtdiagstripe    N
7      64                 Y
8      64                 Y
9      64                 Y


Required Output
Col1 Col2 Col3         Col4    Count
1     100 color:yellow    Y        1
2      64 rtdiagstripe    Y        1
3      64                 Y        2
5      64 rtstripe        N        1
6      64 rtdiagstripe    N        1
7      64                 Y        3


Thanks,
Greg