0

I am creating a quiz maker in my web application. The database consists of the following tables:

  • QUIZ Table: QuizID, Title, Description
  • UserQuiz Table: UserQuizID, QuizID, DateTimeComplete, Score, Username

Now, I want to develop a chart the shows the title of quiz with the number of users who took each one of these quizzes, but I don't know how. I am trying to get a good query for this but I don't know how.

I am using SqlDataSource for accessing the data in the database.

Please help me.

user976711
  • 115
  • 5
  • 16

2 Answers2

2

In SQL this would be something like

SELECT  Q.QuizID, Q.Title, count(uq.*) as Users
  FROM  UserQuiz UQ
  JOIN  Quiz Q ON Q.QuizID = UQ.QuizID
GROUP BY Q.QuizID, Q.Title

or without the table aliases Q and UQ this would be

SELECT  Quiz.QuizID, Title, count(*) as Users
  FROM  UserQuiz
  JOIN  Quiz ON Quiz.QuizID = UserQuiz.QuizID
GROUP BY Quiz.QuizID, Title

using the full table names to distinguish between the two columns called QuizID.

Rup
  • 33,765
  • 9
  • 83
  • 112
  • Could you please simplify your query? I did not understand it especially the letters Q, UQ, uq.* – user976711 Nov 09 '11 at 11:59
  • They're alias table names: `FROM UserQuiz UQ` means use `UQ` as an alias for `UserQuiz` and likewise `Q` for `Quiz`. You need aliases to distinguish between the two columns called because you have a column called QuizID in each table. – Rup Nov 09 '11 at 12:03
1

How about trying something like this

SELECT Title, count(UserQuizID) as usercount
FROM quiz a
JOIN userquiz b on a.quizid = b.quizid
GROUP BY Title

Title would be your XValueMember and usercount YValueMember for the series.

V4Vendetta
  • 37,194
  • 9
  • 78
  • 82