COUNT(*) WITH ROLLUP AND PERCENT

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

Transact-SQL (2000)

COUNT(*) WITH ROLLUP AND PERCENT

Author

Topic

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-14 : 08:28:14

SELECT question,answer,COUNT(answer)
FROM customer GROUP BY question,answer
WITH ROLLUP

WITH ROLLUP is really good stuff

BUT!, i would like to add an extra column that calculates the percent COUNT(answer) off EACH question..

Something like this:

SELECT question,answer,COUNT(answer),(COUNT(answer)/THESUMOFFALLANSWERSFORTHISQUESTION)
FROM customer GROUP BY question,answer
WITH ROLLUP

Hope you get what i mean

Thanks in advance

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-14 : 09:56:22

This would do it for the questions alone. More of the same to include the answers.

SELECT CASE WHEN Grouping(question) = 1
  THEN 'Total'
  ELSE Question
  END As Question , 
  COUNT(Question) As Total,
  (100.0 * COUNT(Question)) / TotalQuestions As Percent
  FROM customer C
  CROSS JOIN (
    SELECT Count(*) As TotalQuestions
      FROM Customer
  ) T
  GROUP BY question
  WITH ROLLUP

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-14 : 11:57:59

Thanks for the tips , but it didnt quite work.. i received an error :(
By the way, im using MS-SQL-server

the table has a lot of question and answers to those questions , i would like it to add the percentage ONLY to to whose answers that are in the same "question-group". Not the percentage of the TOTAL amount of answers in the table.

Like this:

Q:What do you think about oranges.
A:I love it
A:I hate it
A:I dont care

In this "question-group" i start with this:

Q:What do you think about oranges.
A:I love it,7 <--this is total amount of people who choosed this answer.
A:I hate it,2
A:I dont care,3

The total answers in this questiongroup is:(7+2+3)=12
that means that it has to divide every answer with 12

I would like the final product to be like this:
(Except for the calculations)

Q:What do you think about oranges.
A:I love it,7,(7/12*100)=58%
A:I hate it,2,2/12*100)=16%
A:I dont care,3,3/12*100)=25%

And so on and so on with the next questions and answers.. .. . .

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-14 : 13:11:54

Hello, i think im pretty close to the solution :)
this is what i have come up with :D

SELECT questionID,question,answer,COUNT(answer),100.0*COUNT(answer) / (SELECT COUNT(*) FROM customer WHERE questionID=table1.questionID)
FROM customer table1
GROUP BY question,answer,table.questionID

this work very good, but as soon as i add the "WITH ROLLUP" thing underneath it all i get an
"Divide by zero error encountered." ERRROR :( . but how do i do to get around this?

natas
Yak Posting Veteran

51 Posts

Posted - 2005-05-14 : 13:42:03

I tried to add a litte CASE WHEN like this:

COUNT(answer),100.0*COUNT(answer) / CASE WHEN (SELECT COUNT(*) FROM customer WHERE questionID=table1.questionID)<1 THEN 1 ELSE (SELECT COUNT(*) FROM customer WHERE questionID=table1.questionID) END

to put a 1 in case there would be a 0..

The query run but i got all kinds of weird percentage output , 2600% and so on and so on .. hmm.
anybody know THE solution? :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources