15,995,087 members
See more:
dear all,

i have one table Table1 and it column are A,B,C,D....etc .

i need data like select A,Count(distinct Two column(B,C)) * from Table1

how can i get the above Query data ,i dont have any idea how count a distinct field value on base of two field in SQL Server

Posted
Amir Mahfoozi 14-Dec-11 8:12am
Please provide an example to clarify the question.

## Solution 1

Hi,
Try this

Table Structure
SQL
```CREATE TABLE [dbo].[TestingTable](
[Col1] [varchar](50) ,
[Col2] [varchar](50) ,
[Col3] [varchar](50) ,
[Col4] [varchar](50)
)```

Dummy Data
SQL
```1   ABC 123 NULL
1   ABC 124 NULL
2   ABC 123 NULL
2   XYZ 123 NULL
2   ABC 123 NULL
3   XYZ 123 NULL```

SQL Query

SQL
```SELECT Col1, count(forCount)FROM
(SELECT Col1 , convert(VARCHAR, Col2) + convert(VARCHAR, Col3) AS [forCount]
FROM TestingTable GROUP BY Col1, Col2, Col3) tt
GROUP BY Col1```

Return Data

1 2
2 2
3 1

## Solution 3

Hi,

you make me in confusion :) , i think such scenario may not happen,

case 1 : if column A is identical.
in this case distinct of B and C is not possible.

case 2: if column A is not identical.
in this case if column A,B & C are different then distinct is not possible and if all A,B,C are same then it will become normal distinct like
SQL
`select distinct A,B,C from table1`

thanks
-amit.