Counting field value instances

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 2000 Forums

SQL Server Development (2000)

Counting field value instances

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-12 : 23:02:23

Jim writes "I want to query two fields and return a count of the instance of different values for different locations.

Say I store answers to questions and the answer to question one could be 1-4.

I need to count for location1 the number of times question 1 was answered as 1,2,3,or 4, location2 the number of times question 1 was answered as 1,2,3,or 4, location3 the number of times question 1 was answered as 1,2,3,or 4 and so on.

I wrote a view as a test like this:

CREATE VIEW dbo.VIEW2
AS
SELECT tbllocation.loacationName, Q1A1 =
(SELECT COUNT(*)
FROM tblAnwers
WHERE Q1A = 1), Q1A2 =
(SELECT COUNT(*)
FROM tblAnwers
WHERE Q1A = 2), Q1A3 =
(SELECT COUNT(*)
FROM tblAnwers
WHERE Q1A = 3), Q1A4 =
(SELECT COUNT(*)
FROM tblAnwers
WHERE Q1A = 4)
FROM tblAnwers LEFT OUTER JOIN
tbllocation ON
tblAnwers.locationId = tbllocation.locationID
GROUP BY tbllocation.locationName

With this I get counts for answers in locations that didn't answer the number the view says it is counting."

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-12 : 23:20:05

Hey Jim

you said...

quote:
I need to count for location1 the number of times question 1 was answered as 1,2,3,or 4, location2 the number of times question 1 was answered as 1,2,3,or 4, location3 the number of times question 1 was answered as 1,2,3,or 4 and so on.

but did you mean...

quote:
I need to count for location1 the number of times question 1 was answered as 1,2,3,or 4, location2 the number of times question 2 was answered as 1,2,3,or 4, location3 the number of times question 3 was answered as 1,2,3,or 4 and so on.

Do you want this...?
select question, answer, count(answer)
from Tablewithanswers
group by question,answer
order by question, answer

(I'm not really sure you want your results like that...sounds overly complicated to me)

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

Edited by - rrb on 03/12/2002 23:22:04

Jim Miller
Starting Member

3 Posts

Posted - 2002-03-13 : 08:06:28

No I really need to get the number of times location1 answered Question 1 1,2,3, or 4, location2 answered Question 1 1,2,3 or ,4 etc. I think I've got that part figured out,

(SELECT Location AS 'District',
SUM(CASE WHEN Q1 = 1 THEN 1 ELSE 0 END) AS '1',
SUM(CASE WHEN Q1 = 2 THEN 1 ELSE 0 END) AS '2',
SUM(CASE WHEN Q1 = 3 THEN 1 ELSE 0 END) AS '3',
SUM(CASE WHEN Q1 = 4 THEN 1 ELSE 0 END) AS '4',
COUNT(Q1) as 'Total Polled'
GROUP BY location)

Is there a way to calculate percentages without building a temporary table out of this data and and dividing '1' by 'Total Polled'?

Nazim
A custom title

1408 Posts

Posted - 2002-03-13 : 08:19:49

Wouldnt something like this do.

SELECT Location AS 'District',
(SUM(CASE WHEN Q1 = 1 THEN 1 ELSE 0 END) / Count(*)) *100 AS 'Percent 1'
COUNT(Q1) as 'Total Polled'
GROUP BY location

--------------------------------------------------------------

Jim Miller
Starting Member

3 Posts

Posted - 2002-03-13 : 11:24:15

Yes, that works perfect! Thank you!

Jim Miller
Starting Member

3 Posts

Posted - 2002-03-13 : 11:24:28

Yes, that works perfect! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources