SQL query for counting rows with similar columns
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 abov开发者_运维技巧e example the output is expected as
count(seq) col1 col2 col3
2 1 2 3
2 1 2 4
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
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.
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
精彩评论