sql query for counting rows with similar columns

Question

HII Now i have a table with 4 columns as below

seq col1 col2 col3

1 1 2 3

1 1 2 4

2 1 2 3

3 1 2 4

result should be as below

number of seq1 s which have ( col1 col2 col3) same for the above example the output is expected as

count(seq) col1 col2 col3

2 1 2 3

2 1 2 4

Answer 1

Generally when you want to compare similar rows, you need to either select those columns as a result and build a checksum against them or select those rows in some form (concatenated or hash) and join the temp table on itself and select the result set on the remaining rows.

Your question does not have enough information to provide a more complete answer.

Answer 2

Trust, this is what you require..

Select Count(seq) as countseq, col1, col2, col3 from <Table>
group by col1, col2, col3

Or if you have the columns to compare in Table2 then

Select A.col1, A.col2, A.col3, count(B.Seq) from 
     <Table2> as A inner join <Table> as B
     on A.Col1 = B.Col1 and A.Col2=B.Col2 and A.Col3 = B.Col3
     group by A.Col1, A.Col2, A.col3

Answer 3

I think you want to compare col1,col2 and col3 and pick out the count of seq. I concatenated the values of col1,col2 and col3. I cast these columns to varchar and then replaced nulls by '-'. I guess this should help.

Select seq,
ISNULL(Cast(col1 as Varchar(5)),'-')+ISNULL(Cast(col2 as Varchar(5)),'-')+ISNULL(Cast(col3 as Varchar(5)),'-') as tempcol 
into #temp from Table

Select Count(seq) from #temp group by tempcol