I have a table Pro_Search_P.

Nric_fin combinename
--------------------------------------
S7801231 Winnie Wong Vui Tsen
S6801234 WONG VUI TSEN WINNIE
S7801231 Winnie Wong Vui Tsen


My Sql Statement:

SqlStmt = SELECT DISTINCT NRIC_Fin, CombineName
          FROM Pro_Search_P WHERE (CombineName LIKE '%winnie%')

Result:

Nric_fin combinename
--------------------------------------
S6801234 WONG VUI TSEN WINNIE
S7801231 Winnie Wong Vui Tsen

-------------------------------------------------
I got problem in counting the above result.
How can I do the sql statement to get the count = 2 ??

Result:-

Count
------
2
-----------------------------------------------------------------
This is my sqlStatement, I'm not able to get the above result that I want. Please help.

Count statement:

SqlCount = SELECT COUNT(DISTINCT NRIC_Fin) AS COUNT, CombineName
       FROM Pro_Search_P
       WHERE (CombineName LIKE '%winnie%')
       GROUP BY NRIC_Fin, CombineName

Result:

Count CombineName
------------------------------------
1 WONG VUI TSEN WINNIE
1 Winnie Wong Vui Tsen

thanks.

Your Query should look like this to get that result.

select count(*) as NAMECOUNT, CombineName
          FROM Pro_Search_P
          WHERE (CombineName LIKE '%winnie%')
          GROUP BY CombineName

Can add this to the end of the query, if you would like to sort it by the count column

          Order by Count(*)

Cheers
-Vijay

But the sql

select count(*) as NAMECOUNT, CombineName
          FROM Pro_Search_P
          WHERE (CombineName LIKE '%winnie%')
          GROUP BY CombineName

will give me the result of below:-
namecount combineName
---------------------------------
2 Winnie Wong Vui Tsen
1 WONG VUI TSEN WINNIE

----------------------------------------------

I don't need to count the name, all I want is just a count of the distinct records.
How can I get the result:

CountRecords
------------
2

Hope this is what you wanted.

select count(DISTINCT CombineName) as CountRecords
          FROM Pro_Search_P
          WHERE (CombineName LIKE '%winnie%')

Cheers

-Vijay G

Hi Vijay,
Thanks for the answer, it's my mistake to add the nric inside.
thanks.
I got it! ;)

select count(DISTINCT NRIC_Fin) as CountRecords
FROM Pro_Search_P
WHERE (CombineName LIKE '%winnie%')

This query is fine when duplicate value found in NRIC_Fin field.It will avoid.







Enjoy!
Subodh Kumar
Phoneytunes.com

select count(DISTINCT NRIC_Fin) as Count
FROM Pro_Search_P
WHERE (CombineName LIKE '%winnie%')

This query is fine when duplicate value found in NRIC_Fin field.It will avoid.







Enjoy!
Subodh Kumar
Phoneytunes.com

Why don't you try select @@rowcount as "Count' at the end of your query?

Asish