Count(*) to count 0? - 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

General SQL Server Forums

New to SQL Server Programming

Count(*) to count 0?

Author

Topic

salmo
Starting Member

1 Post

Posted - 2006-05-18 : 12:47:47

Hi,

I'm learning sql and am trying out a 'ask the audience' type query whereby for each multiple choice 'question' I want display the results of the people polled:

3 question
4 possible answers to each questions (i.e. a,b,c,d)
6 users with different responses to the question

Obviously I want be able to display these results as such:

for Question 1

4 people choose A
1 person choose B
0 people choose C
1 person choose D

The problem comes with the zero count of people choosing 'C'!

The way I've structure my tables is to have the possible answers in one table and another table with the users responses in.

In the situation where you have a row where answer c pulls a NULL in the user response column, the COUNT(*) counts that unique paring as 1. i.e. there is one NULL, which is correct but is obv not what I want.

Any suggestions on how to go about this?

Do I have to reorganise my tables/data? and can this 'result pull' be done in one SQL query?

Cheers,

Salmo

heres the tables FYI:

"questions" Table

Q_id, Q_text,
1, Q1,
2, Q2,
3, Q3,

"answers" Table

A_id, A_Q_id, A_text,
1, 1, a,
2, 1, b,
3, 1, c,
4, 1, d,
5, 2, a,
6, 2, b,
7, 2, c,
8, 2, d,
9, 3, a,
10, 3, b,
11, 3, c,
12, 3, d,

"Users" Table

U_id, U_Name,
1, Steve,
2, Karen,
3, Dave,
4, Paulie,
5, Getrude,
6, Osama,

"User_repsonse" Table

UR_id, UR_U_id, UR_Q_id, UR_A_id,
1, 1, 1, 1,
2, 1, 2, 6,
3, 1, 3, 11,
4, 2, 1, 1,
5, 2, 2, 7,
6, 2, 3, 11,
7, 3, 1, 2,
8, 3, 2, 5,
9, 3, 3, 12,
10, 4, 1, 1,
11, 4, 2, 5,
12, 4, 3, 9,
13, 5, 1, 4,
14, 5, 2, 8,
15, 5, 3, 10,
16, 6, 1, 1,
17, 6, 2, 6,
18, 6, 3, 11,

Here's the SQL query I use to try and get what I want:

SQL QUERY =
SELECT Questions.Q_text AS question, Answers.A_text AS answer, COUNT(*) AS Frequency FROM ((Answers LEFT OUTER JOIN User_Response ON Answers.A_id=User_Response.UR_A_id) LEFT JOIN Questions ON Answers.A_Q_id=Questions.Q_id) GROUP BY User_response.UR_A_ID ORDER BY Questions.Q_ID,Answers.A_ID

And here's what it produces:

question, answer, Frequency
Q1, a, 4
Q1, b, 1
Q1, c, 1
Q1, d, 1
Q2, a, 2
Q2, b, 2
Q2, c, 1
Q2, d, 1
Q3, a, 1
Q3, b, 1
Q3, c, 3
Q3, d, 1

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2006-05-18 : 20:54:27

Well, there's plenty of ways to skin a yak, but this will do what you want.

select q.q_text, a.a_text, count(ur.ur_id)
from answers a inner join questions q on a.a_q_id = q.q_id
left join user_response ur on a.a_id = ur.ur_a_id and q.q_id = ur.ur_q_id
group by q.q_text, a.a_text
order by q.q_text, a.a_text

returns
Q1 a 4
Q1 b 1
Q1 c 0
Q1 d 1
Q2 a 2
Q2 b 2
Q2 c 1
Q2 d 1
Q3 a 1
Q3 b 1
Q3 c 3
Q3 d 1

note that i used inner join between answers and questions since I suspect you do not want to include any answers with no questions, whether or not responses have been given for them...

other than that you were almost there.

post back if you have another question

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-18 : 20:57:58

I rewrote your query the other way round. INNER JOIN questions and answers and left join to user_response. The diff is count of UR_id instead of count(*). count(*) will count the null. count(UR_id) will only count not null.

select	q.Q_text, a.A_text, count(r.UR_id)
from	questions q inner join answers a
	on	q.Q_id	= a.A_Q_id
	left join user_repsonse r
	on	q.Q_id	= r.UR_Q_id
	and	a.A_id	= r.UR_A_id
group by q.Q_text, a.A_text
order by q.Q_text, a.A_text

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2006-05-18 : 21:05:50

damn - khtan beat me to my final edit :(

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources