How to display the quiz title with the number of users in each quiz in ASP.NET chart?

Question

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.

Answer 1

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.

Answer 2

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.