Count(*) returning 0 value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count(*) returning 0 value

Author

Topic

Alscdz
Starting Member

2 Posts

Posted - 2009-06-20 : 06:09:02

Hello,
i'm writting an SQL query [MYSQL] from 2 tables:

poll_question(id_poll[key], id_answer[key], question)
poll_answer(id_poll[key], id_answer[key], IP[key])

a want to get count of all answers of one poll, something like:

How are you? [id_poll=1]

Fine[id_answer=1, id_poll=1] | 3 [n of answers in table poll_answer]
So-so[id_answer=2, id_poll=1] | 0
Bad[id_answer=3, id_poll=1] | 5

if i write SQL:

SELECT id_answer, COUNT(*) cnt
FROM poll_answer
WHERE id_poll = 1
GROUP BY id_answer;

i don't get id_answer=2, because it is 0. I tries some different subselects, joins... but did not find the solution.

Can you help me write this - for some of you - easy select?

thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-20 : 06:19:09

Do note that this is a MS SQL Server forum. For MYSQL, try posting at dbforums.com

However, this should works in MYSQL also


select q.id_answer, count(a.id_answer) as cnt
from   poll_question q
       left join poll_answer a on q.id_poll = a.id_poll and q.id_answer = a.id_answer
where  q.id_poll = 1
group by q.id_answer

KH
[spoiler]Time is always against us[/spoiler]

Alscdz
Starting Member

2 Posts

Posted - 2009-06-20 : 06:33:26

khtan thank you.

First, i didn't realize it's MSsql forum, because it's "sqlteam.com"
Second, thank you for the select statement it works! I had written the same, except I used COUNT(*) and it returns different result. Thank you again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-20 : 06:59:58

you are welcome.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources