Is this possible - Percentages, etc from query. -- inetserver asp db

Shawn
9/30/2004 11:30:46 AM

Just out of curiosity for my own understanding, what are
the limitations? Is it that you would have to have 2
different select statements or something similar?

I agree it is easy in ASP, thought I try to do it all in
SQL for kicks.

Thanks Aaron.

[quoted text, click to view]

Aaron [SQL Server MVP]
9/30/2004 2:11:48 PM

You can do that in a query, but I think it would be easier to do it in ASP
once you have all the results.

--
http://www.aspfaq.com/
(Reverse address to reply.)

[quoted text, click to view]

Bob Barrows [MVP]
9/30/2004 2:34:22 PM

[quoted text, click to view]

Yes, it's possible to do it in one query. However, I would do it like this
(it will be more efficient):

DECLARE @recCount int
SET @recCount=(SELECT Count(Question1) FROM MyQuestions);
SELECT
Count(Question1) AS [Question #1],
Response = CASE(Question1)
WHEN 1 THEN 'Good'
WHEN 2 THEN 'OK'
WHEN 3 THEN 'Bad'
ELSE 'DID NOT ANSWER'
End,
100*Count(Question1)/@recCount As [Percent]
FROM MyQuestions GROUP BY Question1
ORDER BY Question1

Don't you want to order by Count(Question1)?

Even though this can be sent as a batch using dynamic sql, I would
encapsulate it in a stored procedure.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Aaron [SQL Server MVP]
9/30/2004 2:46:15 PM

This is not an optimal solution because you have to run an aggregate
subquery for every grouping in the result (ugh).

CREATE TABLE Responses
(
RID INT IDENTITY,
Question1 INT,
Question2 INT,
Question3 INT
)
GO

SET NOCOUNT ON
INSERT Responses(Question1, Question2, Question3)
SELECT 1,2,3
INSERT Responses(Question1, Question2, Question3)
SELECT 3,2,1
INSERT Responses(Question1, Question2, Question3)
SELECT 2,3,1
INSERT Responses(Question1, Question2, Question3)
SELECT 2,3,1
INSERT Responses(Question1, Question2, Question3)
SELECT NULL,1,2
INSERT Responses(Question1, Question2, Question3)
SELECT NULL,NULL,NULL
INSERT Responses(Question1, Question2, Question3)
SELECT NULL,3,NULL
GO

SELECT
[#] = COUNT(*),
[%] = CONVERT
(
DECIMAL(4,2),
100.0*COUNT(*) /
(SELECT COUNT(*) FROM Responses)
),
Answer = CASE(Question1)
WHEN 1 THEN 'Good'
WHEN 2 THEN 'OK'
WHEN 3 THEN 'Bad'
ELSE 'DID NOT ANSWER'
END
FROM
Responses r
GROUP BY
Question1
ORDER BY
Question1

GO

DROP TABLE Responses

--
http://www.aspfaq.com/
(Reverse address to reply.)

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about inetserver asp db

inetserver asp db : Is this possible - Percentages, etc from query.