[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help on select with group by statement

Posted on 2008-11-05
3
Medium Priority
?
631 Views
Last Modified: 2012-06-27
I am trying to figure out how to group some results from a survey.

I have 4 tables: SurveyResults, SurveyAnswers, SurveyQuestions, and Users

SurveyResults
---------------
results_id
UserID
question_id
answer_id
date_inserted

SurveyAnswers
---------------
answer_id
question_id
answer
anwser_type
order_in_question

SurveyQuestions
---------------
question_id
survey_id
question
question_type
default_answer
order_in_survey

Users
---------------
UserID
Company
Phone

I have this select statement that works but does not group properly:

sub db_select_grid_SurveyResults
      grid_SurveyResults_sql = "SELECT " & _
      "Users.Company," & _
      "Users.Phone," & _
      "SurveyResults.results_id, " & _
      "SurveyResults.UserID, " & _
      "SurveyResults.answer_value, " & _
      "SurveyResults.dt_inserted, " & _
      "SurveyAnswers.question_id, " & _
      "SurveyAnswers.answer_id, " & _       
      "SurveyAnswers.answer, " & _
      "SurveyAnswers.order_in_question, " & _
      "SurveyQuestions.question, " & _
      "SurveyQuestions.survey_id FROM (((SurveyResults LEFT JOIN SurveyAnswers ON SurveyAnswers.answer_id = SurveyResults.answer_id) " & _
      "LEFT JOIN SurveyQuestions ON SurveyQuestions.question_id = SurveyAnswers.question_id) " & _
      "LEFT JOIN Users On SurveyResults.UserID=Users.UserID) ORDER BY SurveyResults.UserID, SurveyAnswers.order_in_question" & _
      "" & _
      "" & _
      ""
end sub


if grid_SurveyResults_sql <> "" then
      cmd.CommandText = grid_SurveyResults_sql
      rs.Filter = ""
      if survey_id <> "" then rs.Filter = "survey_id = " & to_sql(survey_id,"number")
      rs.CursorLocation = 3
      rs.CacheSize = 5
      rs.Open cmd
      if not rs.EOF then
                  rs.MoveFirst
                  rs.PageSize            = 30
                  totalPages             = rs.PageCount
                  rs.AbsolutePage      = curPage
                  num_recs = rs.RecordCount
                  results = true
      else
            results = false
            rs.Close
      end if
else
      results = false
end if


The above SQL produces:

UserID      Company            Phone                  Date            Question                  Answer       
--------------------------------------------------------------------------------------------
1            Company1      111-222-3333      10/27/2008      How many dogs?            7
1            Company1      111-222-3333      10/27/2008      How many cats?            3
1            Company1      111-222-3333      10/27/2008      How many birds?            1
2            Company2      111-222-3333      11/02/2008      How many dogs?            5
2            Company2      111-222-3333      11/02/2008      How many cats?            3
2            Company2      111-222-3333      11/02/2008      How many birds?            1
3            Company3      111-222-3333      10/16/2008      How many dogs?            1
3            Company3      111-222-3333      10/16/2008      How many cats?            3
3            Company3      111-222-3333      10/16/2008      How many birds?            1



Need data displayed like:

UserID      Company            Phone                  Date            Question                  Answer       
--------------------------------------------------------------------------------------------
1            Company1      111-222-3333      10/27/2008      How many dogs?            7
                                                                        How many cats?            3
                                                                        How many birds?            1
2            Company2      111-222-3333      11/02/2008      How many dogs?            5
                                                                        How many cats?            3
                                                                        How many birds?            1
3            Company3      111-222-3333      10/16/2008      How many dogs?            1
                                                                        How many cats?            3
                                                                        How many birds?            1

Each user has multiple questions and answers. Can anyone help me modify my select statement (or code) to group the results properly?
0
Comment
Question by:ChristinaPupo
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
golfDoctor earned 2000 total points
ID: 22895314
you would store the userid in a variable in each loop, and check each time if the userid is different that the previous record userid.  if it is different that display the full information for the line.  if userid is same as last record, then only display the question/answers information on the line.

example
<%

olderuserid = 0 'default userid

do until rs.eof
  newuserid = rs("userid") ' new user id
   
   if newuserid <> olduserid then  'compare old and new
        response.write "full information"
   else
        response.write "questions/answer info"
   end if
    olduserid = newuserid 'reset old userid with newuserid for next record
rs.movenext
loop
%>
0
 

Author Comment

by:ChristinaPupo
ID: 22897390
So simple I never even considered it, lol. I will give it a try and get back to you.
0
 

Author Closing Comment

by:ChristinaPupo
ID: 31513757
Thank you!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question