Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 19th, 2003, 07:01 AM
Authorized User
 
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql Count

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.

 
Old October 19th, 2003, 07:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old October 19th, 2003, 09:06 PM
Authorized User
 
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old October 20th, 2003, 12:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hope this is what you wanted.

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

Cheers

-Vijay G
 
Old October 20th, 2003, 03:33 AM
Authorized User
 
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old October 21st, 2003, 08:16 AM
Authorized User
 
Join Date: Oct 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SubodhKumar Send a message via Yahoo to SubodhKumar
Default

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
 
Old October 21st, 2003, 08:17 AM
Authorized User
 
Join Date: Oct 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SubodhKumar Send a message via Yahoo to SubodhKumar
Default

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
 
Old November 7th, 2003, 02:01 PM
Registered User
 
Join Date: Nov 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Asish






Similar Threads
Thread Thread Starter Forum Replies Last Post
sql count problem keyvanjan Classic ASP Basics 1 May 5th, 2006 09:37 AM
need help for sql COUNT designwork SQL Language 4 December 29th, 2005 05:26 PM
sql COUNT problem jordancrandall SQL Language 6 October 18th, 2004 10:27 AM
SQL query for count programmer_kay ADO.NET 1 April 18th, 2004 02:48 AM
SQL query for count programmer_kay SQL Language 1 April 17th, 2004 07:30 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.