The sql (Count) is displaying 1 in stead of 0

Question

0.00/5 (No votes)

See more:

SQL

select CANDIDATE.Candidate_Party, COUNT(CANDIDATE.Candidate_Number) AS VotesNumber
from CANDIDATE
left outer join VOTE on CANDIDATE.Candidate_Number = VOTE.Candidate_Number
WHERE
CANDIDATE.Candidate_Portfolio = 'Missionvale Representative'

 GROUP BY CANDIDATE.Candidate_Party

My sql above, everytime when it counting the there is no data in a database is display 1, what i want is to return a 0 if there is no data.

i tryed lot of querys but they ddnt work. Please help.

When m counting it must display 0

Posted 28-Aug-13 23:29pm

210032278

Add a Solution

Comments

[no name] 29-Aug-13 5:51am

http://www.codeproject.com/Questions/643296/How-to-count-and-display-0

nikhil-vartak 29-Aug-13 5:52am

Your requirement is not clear. Can you re-phrase it to make it more understandable?

ZurdoDev 29-Aug-13 7:48am

It shows 1 because there is 1 record. It is likely coming from your left join. Remove the COUNT() and the group by and see what comes back. You should get 1 record.

Bernhard Hiller 29-Aug-13 9:59am

Do not know the exact syntax now. You need two subqueries: subquery 1 is
SELECT DISTINCT Candidate_Party FROM Candidate, subquery 2 is essentially your query above (replace LEFT JOIN with OUTER JOIN).
Then you need to join the results of the two subqueries, with a CASE statement selecting 0 as the count when subquery 2 does not contain a corresponding record.
Hope someone else is so fluent with SQL to write that down.

1 solution

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)

Kschuler · Answer 1 · 2013-08-29T03:54:00

I think it's because of the type of join you are using. A left outer join is ALWAYS going to give you EVERY candidate (because it's the table on the left) and then will also give you any votes they made. What you want is an inner join, which will only give you data that matches both files.

http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join[^]

http://www.google.com/#q=sql+outer+join+vs+inner+join[^]