Bobski Bobski - 3 months ago 7
SQL Question

SQL Server query distinct

I'm trying to do a query in SQL Server 2008. This is the structure of my table

Recno ClientNo ServiceNo
---------------------------------
1234 17 27
2345 19 34
3456 20 33
4567 17 34


I'm trying to select
RecNo
however, filtering by distinct
ClientNo
, so for some clients such as client no 17 - they have more than 1 entry, I'm trying to count that client only once. So basically, looking at this table, I'm only supposed to see 3 RecNo's, since there are only 3 distinct clients. Please help

Select RecNo, Count(ClientNo)
from TblA
where Count(clientNo)<2


Something like this?

EDIT:

The value of
RecNo
is not relevant, I only need to have an accurate number of records. In this case, I'd like to have 3 records.

Answer

oaky you are getting some crazy answers probably becuase your desired result is not clear so I suggest if some of these are not what you need that you clarify your desired result.

If you want the answer 3, I can only assume you want a count of DISTINCT ClientNo's if so it is simply aggregation.

SELECT COUNT(DISTINCT ClientNo) as ClientNoDistinctCount
FROM
    TblA
GROUP BY
    ClientNo
Comments