To determine the count in the table

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi all,
I need to get the count of single table from the join of two tables.
The query is mentioned below.

SELECT COUNT('*') FROM student_main  main	(Nolock), address arc (nolock)
WHERE  arc.id		= 20
and    main.stu_no 	= arc.stu_no

From the above query, i need the count of student_main table. But it gets the count of both tables.
Kindly advise...

Regards,
Ismail

Posted 14-Apr-11 18:14pm

mugamath

Updated 14-Apr-11 18:46pm

m@dhu

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-04-14T19:03:00

If you join two tables and use count, you'll have the count of the result set, not the table. In your example you would get the total amount of students whose address have id 20.

So if you want to get the total count separately, you can use for example scalar query for that, like:

SELECT main.*,
       arc.*,
       (SELECT COUNT(*) FROM student_main) AS TotalStudentAmount
FROM student_main  main, 
     address arc
WHERE  arc.id       = 20
AND    main.stu_no  = arc.stu_no

Another thing, don't use nolock unless you really have to (for good reasons).

Pong D. Panda · Answer 2 · 2011-04-14T19:04:00

Solution 3

Try this baby boy.

SQL

SELECT COUNT(*) FROM student_main  main   
WHERE main.stu_no in 
   (SELECT stu_no FROM address WHERE id = 20)

Good luck!

Posted 14-Apr-11 19:04pm

Pong D. Panda

Comments

Pong D. Panda 17-Apr-11 21:53pm

Whoever downvoted my answer, can you further explain why?