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)
 Aggregate function headaches

Author  Topic 

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 15:53:12
I have a rather advanced query that I am having a problem with at the moment There are two pieces, one is to find the count of how many survey responders answered a particular question a certain way. That query is summarized as such:


  SELECT DISTINCT 
   TOP 100 PERCENT 
   dbo.tblSurveyQuestionChoices.questionID,
   dbo.tblSurveyQuestionChoices.questionChoiceID AS ResponseID,
   CONVERT(varchar(255), dbo.tblSurveys.surveyName) AS [Survey Name]
   CONVERT(varchar(255), dbo.tblSurveyQuestions.question) AS Question,
   CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice) AS Choice,
   COUNT(*) AS [Count]
  FROM
   dbo.tblSurveyUserResponses INNER JOIN
   dbo.tblSurveys INNER JOIN
   dbo.tblSurveyQuestions ON dbo.tblSurveys.surveyID = dbo.tblSurveyQuestions.surveyID
   INNER JOIN dbo.tblSurveyQuestionChoices ON
   dbo.tblSurveyQuestions.questionID = dbo.tblSurveyQuestionChoices.questionID ON 
   dbo.tblSurveyUserResponses.questionChoiceID = dbo.tblSurveyQuestionChoices.questionChoiceID AND 
   bo.tblSurveyUserResponses.questionID = dbo.tblSurveyQuestionChoices.questionID INNER JOIN
   dbo.tblSurveyRequests INNER JOIN
   dbo.tblSurveyUserInfo ON dbo.tblSurveyRequests.surveyRequestID = dbo.tblSurveyUserInfo.surveyRequestID ON
   dbo.tblSurveyUserResponses.surveyUserID = dbo.tblSurveyUserInfo.surveyUserID
  GROUP BY
   dbo.tblSurveyQuestionChoices.questionChoiceID,
   dbo.tblSurveyQuestionChoices.questionID,
   CONVERT(varchar(255), dbo.tblSurveyQuestions.question), 
   CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice),
   CONVERT(varchar(255), dbo.tblSurveys.surveyName)
  ORDER BY
   dbo.tblSurveyQuestionChoices.questionChoiceID


I know this may not be the most effecient way, but there's a lot of joined tables that I have to use, each containing key information to display. As it stands, this query returns each answer from each question and the count of how many respondents selected each given answer. I have this saved as a view, we'll call it VIEW_Survey_Results

Now I want to also include in this query the percentage of responders for a given a question that answered a certain way. Example:


QuestionID    AnswerID    Count
1             10          5
1             11          3
1             12          1
2             13          3
2             14          2

This is what my original query will return with some other information like the text of the question & choices.

I want to add another column like so:

QuestionID    AnswerID    Count   Percentage
1             10          5       55.55
1             11          3       33.33
1             12          1       11.11
2             13          3       60.00
2             14          2       40.00

I'm fine with formatting the percentage anyway. The logic here is to sum up all the counts for QuestionID and divide each particular count by that sum, this gives you the percentage.

So far, I have this adapted code (using derived tables because of the issue with aggregate functions in SQL server ([url]http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspx[/url]):


SELECT 
  t.*, sum(t.Count) as SumCount
FROM
(
SELECT DISTINCT 
   TOP 100 PERCENT 
   dbo.tblSurveyQuestionChoices.questionID,
   dbo.tblSurveyQuestionChoices.questionChoiceID AS ResponseID,
   CONVERT(varchar(255), dbo.tblSurveys.surveyName) AS [Survey Name]
   CONVERT(varchar(255), dbo.tblSurveyQuestions.question) AS Question,
   CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice) AS Choice,
   COUNT(*) AS [Count]
FROM
   dbo.tblSurveyUserResponses INNER JOIN
   dbo.tblSurveys INNER JOIN
   dbo.tblSurveyQuestions ON dbo.tblSurveys.surveyID = dbo.tblSurveyQuestions.surveyID
   INNER JOIN dbo.tblSurveyQuestionChoices ON
   dbo.tblSurveyQuestions.questionID = dbo.tblSurveyQuestionChoices.questionID ON 
   dbo.tblSurveyUserResponses.questionChoiceID = dbo.tblSurveyQuestionChoices.questionChoiceID AND 
   bo.tblSurveyUserResponses.questionID = dbo.tblSurveyQuestionChoices.questionID INNER JOIN
   dbo.tblSurveyRequests INNER JOIN
   dbo.tblSurveyUserInfo ON dbo.tblSurveyRequests.surveyRequestID = dbo.tblSurveyUserInfo.surveyRequestID ON
   dbo.tblSurveyUserResponses.surveyUserID = dbo.tblSurveyUserInfo.surveyUserID
GROUP BY
   dbo.tblSurveyQuestionChoices.questionChoiceID,
   dbo.tblSurveyQuestionChoices.questionID,
   CONVERT(varchar(255), dbo.tblSurveyQuestions.question), 
   CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice),
   CONVERT(varchar(255), dbo.tblSurveys.surveyName)
ORDER BY
   dbo.tblSurveyQuestionChoices.questionChoiceID
) t
GROUP BY
  t.Question, t.[Survey Name], 
  t.[Choice], t.ResponseID, t.QuestionID


I understand this will only give me the sum, not the percentage, but that's just a matter of dividing once I get the sum. However, I DO NOT get the sum, I get the following error:

quote:
Server: Msg 8120, Level 16, State 1, Line 1
Column 't.Count' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


This is where I am stuck, I don't understand the error because t.Count is in a SUM(...) function, which as far as I understood is an aggregate function. If I add "..., t.Count" to my outer GROUP BY, then the Sum(...) just returns the same as t.Count--not very useful.

I'm a bit out of my league here, but trying to piece this together. Any suggestions are greatly appreciated.

Thank you,

Ryan

tm
Posting Yak Master

160 Posts
Posted - 2007-10-18 : 16:04:54
Your on the right track but you just need to change the first line ..

From:
SELECT t.*, sum(t.Count) as SumCount

To
SELECT t.Question, t.[Survey Name], t.[Choice], t.ResponseID, t.QuestionID, sum(t.Count) as SumCount

The problem is not with the sum(t.count) it's "t.*" as this includes t.Count as a column to display and it's not in the group list.
Go to Top of Page

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 16:09:25
quote:
Originally posted by tm

Your on the right track but you just need to change the first line ..

From:
SELECT t.*, sum(t.Count) as SumCount

To
SELECT t.Question, t.[Survey Name], t.[Choice], t.ResponseID, t.QuestionID, sum(t.Count) as SumCount

The problem is not with the sum(t.count) it's "t.*" as this includes t.Count as a column to display and it's not in the group list.




Thanks a lot for your feedback tm, and yes you are right, that does take away the error. The issue I'm having when I make this change is that Sum(t.Count) is the same as t.Count, perhaps I have my GROUP BY incorrect. Do you have any suggestions on what I should change in order to include both t.Count and SUM(t.Count) where
SUM(t.Count) is the sum of the t.Count for each answer with respect to the corresponding question

For example, in my examples in the original post, for QuestionID=1, SUM(t.Count) should be 9 and for QuestionID=2, SUM(t.Count) should be 5.

Does this make sense?
Go to Top of Page

tm
Posting Yak Master

160 Posts
Posted - 2007-10-18 : 16:33:42
You can try this .... BTW I did not test this ..

(Using WITH (refer to thread .. "using Count(*) with INNER JOIN"). Peso and Kristen gives great solutions as I learned this today.
Will use WITH more often)


-- Code starts here ==================
WITH CTE (Question, SurveyName, [Choice], ResponseID, QuestionID, SumCount) AS
(
SELECT
t.Question, t.[Survey Name],
t.[Choice], t.ResponseID, t.QuestionID, t.[Count]
FROM
(
SELECT DISTINCT
TOP 100 PERCENT
dbo.tblSurveyQuestionChoices.questionID,
dbo.tblSurveyQuestionChoices.questionChoiceID AS ResponseID,
CONVERT(varchar(255), dbo.tblSurveys.surveyName) AS [Survey Name]
CONVERT(varchar(255), dbo.tblSurveyQuestions.question) AS Question,
CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice) AS Choice,
COUNT(*) AS [Count]
FROM
dbo.tblSurveyUserResponses INNER JOIN
dbo.tblSurveys INNER JOIN
dbo.tblSurveyQuestions ON dbo.tblSurveys.surveyID = dbo.tblSurveyQuestions.surveyID
INNER JOIN dbo.tblSurveyQuestionChoices ON
dbo.tblSurveyQuestions.questionID = dbo.tblSurveyQuestionChoices.questionID ON
dbo.tblSurveyUserResponses.questionChoiceID = dbo.tblSurveyQuestionChoices.questionChoiceID AND
bo.tblSurveyUserResponses.questionID = dbo.tblSurveyQuestionChoices.questionID INNER JOIN
dbo.tblSurveyRequests INNER JOIN
dbo.tblSurveyUserInfo ON dbo.tblSurveyRequests.surveyRequestID = dbo.tblSurveyUserInfo.surveyRequestID ON
dbo.tblSurveyUserResponses.surveyUserID = dbo.tblSurveyUserInfo.surveyUserID
GROUP BY
dbo.tblSurveyQuestionChoices.questionChoiceID,
dbo.tblSurveyQuestionChoices.questionID,
CONVERT(varchar(255), dbo.tblSurveyQuestions.question),
CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice),
CONVERT(varchar(255), dbo.tblSurveys.surveyName)
ORDER BY
dbo.tblSurveyQuestionChoices.questionChoiceID
) t
GROUP BY
t.Question, t.[Survey Name],
t.[Choice], t.ResponseID, t.QuestionID

)

SELECT Question, SurveyName, [Choice], ResponseID, QuestionID, SumCount, TotalQuestionCount
FROM CTE CTE1
INNER JOIN
(SELECT Question, sum(Question) as TotalQuestionCount FROM CTE GROUP BY Question) CTE22
ON CTE1.Question = CTE22.Question

-- Code Ends here ================
Go to Top of Page

tm
Posting Yak Master

160 Posts
Posted - 2007-10-18 : 16:34:59
Sorry .. The last bit should be ..

SELECT Question, SurveyName, [Choice], ResponseID, QuestionID, SumCount, TotalQuestionCount
FROM CTE CTE1
INNER JOIN
(SELECT Question, sum(SumCount) as TotalQuestionCount FROM CTE GROUP BY Question) CTE22
ON CTE1.Question = CTE22.Question
Go to Top of Page

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 16:39:04
I've made *some* progress, but I'm still not there. I was able to get the SUM(t.Count) to return the correct results by stripping down the columns I'm pulling so that I can reduce the GROUP BY clause like so:


SELECT 
   t.QuestionID,  sum(t.Count) as SumCount
FROM
(
  SELECT DISTINCT 
   TOP 100 PERCENT 
   dbo.tblSurveyQuestionChoices.questionID, 
   dbo.tblSurveyQuestionChoices.questionChoiceID AS ResponseID, 
   dbo.tblSurveys.surveyName AS [Survey Name],
   CONVERT(varchar(255), dbo.tblSurveyQuestions.question) AS Question, 
   CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice) AS Choice,
   COUNT(*) AS [Count]
  FROM
   dbo.tblSurveyUserResponses INNER JOIN
   dbo.tblSurveys INNER JOIN
   dbo.tblSurveyQuestions ON dbo.tblSurveys.surveyID = dbo.tblSurveyQuestions.surveyID INNER JOIN
   dbo.tblSurveyQuestionChoices ON dbo.tblSurveyQuestions.questionID = dbo.tblSurveyQuestionChoices.questionID ON 
   dbo.tblSurveyUserResponses.questionChoiceID = dbo.tblSurveyQuestionChoices.questionChoiceID AND
   dbo.tblSurveyUserResponses.questionID = dbo.tblSurveyQuestionChoices.questionID INNER JOIN
   dbo.tblSurveyRequests INNER JOIN
   dbo.tblSurveyUserInfo ON
   dbo.tblSurveyRequests.surveyRequestID = dbo.tblSurveyUserInfo.surveyRequestID ON 
   dbo.tblSurveyUserResponses.surveyUserID = dbo.tblSurveyUserInfo.surveyUserID

  GROUP BY
   dbo.tblSurveyQuestionChoices.questionChoiceID, 
   dbo.tblSurveyQuestionChoices.questionID,
   CONVERT(varchar(255), dbo.tblSurveyQuestions.question),
   CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice), 
   dbo.tblSurveys.surveyName
  ORDER BY
   dbo.tblSurveyQuestionChoices.questionChoiceID
) t
GROUP BY t.QuestionID


However, I need the other information included in the resulting set, but when I try to add other columns (like t.Count, ResponseID for example), I have to add it to the ORDER BY clause, which then makes the SUM(t.Count) incorrect.

Any suggestions on how to pull the extra information without having to put it all into the ORDER BY clause, or maybe a simpler way of achieving the desired results?

Thanks again for any help.

--
Ryan
Go to Top of Page

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 16:40:22
quote:
Originally posted by tm

Sorry .. The last bit should be ..

SELECT Question, SurveyName, [Choice], ResponseID, QuestionID, SumCount, TotalQuestionCount
FROM CTE CTE1
INNER JOIN
(SELECT Question, sum(SumCount) as TotalQuestionCount FROM CTE GROUP BY Question) CTE22
ON CTE1.Question = CTE22.Question


Thanks TM, my latest reply is some stuff I've done on my own, I will try out what you suggest and see if that helps. I did read through that post by Kristen et al, but it was a bit out of my league and using stored procedures (which I am not).
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-10-18 : 16:54:40
[code]
Declare @T Table (QuestionID int, AnswerID int, Counts int)
Insert into @T
SELECT 1 , 10 , 5 union all
SELECT 1 , 11 , 3 union all
SELECT 1 , 12 , 1 union all
SELECT 2 , 13 , 3 union all
SELECT 2 , 14 , 2

Select * , Per = Convert(decimal(10,2),Convert(decimal(10,2),Counts)/(Select Convert(decimal(10,2),sum(Counts)) From @T T2 Where T2.QuestionID = T.QuestionID ) * 100)
from @T T
[/code]


Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 17:40:27
quote:
Originally posted by dinakar


Declare @T Table (QuestionID  int, AnswerID int, Counts int)
Insert into @T 
SELECT 1 ,            10   ,       5 union all
SELECT 1 ,            11   ,       3 union all
SELECT 1 ,            12   ,       1 union all
SELECT 2 ,            13   ,       3 union all
SELECT 2 ,            14   ,       2

...



Thanks for your reply dinakar. I sorta understand what you're suggesting, but I believe it won't work for my situation because my "Count" is not stored in the database. I use the COUNT(*) function, which I believe is what is causing so much frustration. Perhaps you could still offer some advice if we changed your table structure to be the following:


Declare @T Table (QuestionID  int, AnswerID int, UserID int)
Insert into @T 
SELECT 1 ,            10   ,       1 union all
SELECT 1 ,            10   ,       2 union all
SELECT 1 ,            11   ,       3 union all
SELECT 2 ,            12   ,       1 union all
SELECT 2 ,            13   ,       2


With this structure, the Count for QuestionID=1 & AnswerID=10 would be 2 (UserID=1 & UserID=2), and the Count for QuestionID=1 & AnswerID=11 would be 1. Likewise, the Count for QuestionID=2 & AnswerID=12 would be 1, and so would QuestionID=2 & AnswerID=13.

Given this setup, can you offer advice on how to pull the count & sum/percentage like you did in your example?

Thanks again for all your help.

--
Ryan
Go to Top of Page

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 18:03:37
quote:
Originally posted by tm

You can try this .... BTW I did not test this ..

(Using WITH (refer to thread .. "using Count(*) with INNER JOIN"). Peso and Kristen gives great solutions as I learned this today.
Will use WITH more often)


Hello tm:

I have been working with what you've given me, starting to understand it. The problem I'm having is no matter what I do, I get the error: Incorrect syntax near the keyword 'WITH'

Even if I try a very basic example with another table, I get the same error. I'm using SQL Server 2000, is 'WITH' availalbe in 2000?
Go to Top of Page

dageyra
Starting Member

7 Posts
Posted - 2007-10-18 : 18:11:07
I just realized I've posted this in the SQL Server 2005 forum. I am using SQL Server 2000 and will repost accordinly. I apologize for any inconvenience.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts
Posted - 2007-10-18 : 19:51:36
Moved to the T-SQL 2000 forum at the posters request.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-10-18 : 19:55:50
quote:
Originally posted by dageyra

quote:
Originally posted by dinakar


Declare @T Table (QuestionID  int, AnswerID int, Counts int)
Insert into @T 
SELECT 1 ,            10   ,       5 union all
SELECT 1 ,            11   ,       3 union all
SELECT 1 ,            12   ,       1 union all
SELECT 2 ,            13   ,       3 union all
SELECT 2 ,            14   ,       2

...



Thanks for your reply dinakar. I sorta understand what you're suggesting, but I believe it won't work for my situation because my "Count" is not stored in the database. I use the COUNT(*) function, which I believe is what is causing so much frustration. Perhaps you could still offer some advice if we changed your table structure to be the following:


Declare @T Table (QuestionID  int, AnswerID int, UserID int)
Insert into @T 
SELECT 1 ,            10   ,       1 union all
SELECT 1 ,            10   ,       2 union all
SELECT 1 ,            11   ,       3 union all
SELECT 2 ,            12   ,       1 union all
SELECT 2 ,            13   ,       2


With this structure, the Count for QuestionID=1 & AnswerID=10 would be 2 (UserID=1 & UserID=2), and the Count for QuestionID=1 & AnswerID=11 would be 1. Likewise, the Count for QuestionID=2 & AnswerID=12 would be 1, and so would QuestionID=2 & AnswerID=13.

Given this setup, can you offer advice on how to pull the count & sum/percentage like you did in your example?

Thanks again for all your help.

--
Ryan


You can get the results of the view, along with the count into a table variable and do a final select from the table variable as per my script.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

tm
Posting Yak Master

160 Posts
Posted - 2007-10-19 : 09:16:34
Dageyra .. If you have found a solution you can ignore this. This will work for SQL 2000. You can create a view with your original select statement and then use the view to retrieve the percentage. Some like (below not tested) ..

Create View CTE
AS
SELECT
t.Question, t.[Survey Name],
t.[Choice], t.ResponseID, t.QuestionID, t.[Count] AS SumCount
FROM
(
SELECT DISTINCT
TOP 100 PERCENT
dbo.tblSurveyQuestionChoices.questionID,
dbo.tblSurveyQuestionChoices.questionChoiceID AS ResponseID,
CONVERT(varchar(255), dbo.tblSurveys.surveyName) AS [Survey Name],
CONVERT(varchar(255), dbo.tblSurveyQuestions.question) AS Question,
CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice) AS Choice,
COUNT(*) AS [Count]
FROM
dbo.tblSurveyUserResponses INNER JOIN
dbo.tblSurveys INNER JOIN
dbo.tblSurveyQuestions ON dbo.tblSurveys.surveyID = dbo.tblSurveyQuestions.surveyID
INNER JOIN dbo.tblSurveyQuestionChoices ON
dbo.tblSurveyQuestions.questionID = dbo.tblSurveyQuestionChoices.questionID ON
dbo.tblSurveyUserResponses.questionChoiceID = dbo.tblSurveyQuestionChoices.questionChoiceID AND
bo.tblSurveyUserResponses.questionID = dbo.tblSurveyQuestionChoices.questionID INNER JOIN
dbo.tblSurveyRequests INNER JOIN
dbo.tblSurveyUserInfo ON dbo.tblSurveyRequests.surveyRequestID = dbo.tblSurveyUserInfo.surveyRequestID ON
dbo.tblSurveyUserResponses.surveyUserID = dbo.tblSurveyUserInfo.surveyUserID
GROUP BY
dbo.tblSurveyQuestionChoices.questionChoiceID,
dbo.tblSurveyQuestionChoices.questionID,
CONVERT(varchar(255), dbo.tblSurveyQuestions.question),
CONVERT(varchar(255), dbo.tblSurveyQuestionChoices.choice),
CONVERT(varchar(255), dbo.tblSurveys.surveyName)
ORDER BY
dbo.tblSurveyQuestionChoices.questionChoiceID
) t
GROUP BY
t.Question, t.[Survey Name],
t.[Choice], t.ResponseID, t.QuestionID

==========

Then use the view to get your fields for the percentage ....

SELECT Question, SurveyName, [Choice], ResponseID, QuestionID, SumCount, TotalQuestionCount,
Convert(decimal(10,2), Convert(decimal(10,2),SumCount)/(Convert(decimal(10,2),TotalQuestionCount) * 100) as PercentQuestion
FROM CTE CTE1
INNER JOIN
(SELECT Question, sum(SumCount) as TotalQuestionCount FROM CTE GROUP BY Question) CTE22
ON CTE1.Question = CTE22.Question
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site