# question

## How to come up with statistics that shows the total number of quizzes, total nubmer of participants?

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
+1 for asking an interesting question *and* including test data/structures too.
0 Likes 0 ·

·
After re-reading the question, I'm not too certain on whether this will do what you really want, but it should get you started. SELECT Q.QuizID, Q.Title, COUNT(UQ.QuizID) AS Users, SUM(CASE WHEN UQ.DateTimeComplete >= DATEADD(week, -1, GETDATE()) THEN 1 ELSE 0 END) ParticipantsLastWeek, SUM(CASE WHEN UQ.DateTimeComplete >= DATEADD(month, -1, GETDATE()) THEN 1 ELSE 0 END) ParticipantsLastMonth, SUM(CASE WHEN UQ.DateTimeComplete >= DATEADD(year, -1, GETDATE()) THEN 1 ELSE 0 END) ParticipantsLastYear FROM dbo.Quiz AS Q LEFT JOIN dbo.UserQuiz AS UQ ON Q.QuizID = UQ.QuizID GROUP BY Q.QuizID, Q.Title The main thing to note is that I use a "trick" to count the data. As luck would have it, I blogged about this very idea about an hour ago: [When COUNT() isn't the only way to count][1]. You need to pay attention to the date logic. I am assuming that the `DATEPART` calculations are ok for what you want. This may not be the case and can be changed as needed. If you need further help, or this query isn't enough, let me know. [1]: http://williamdurkin.wordpress.com/2011/11/17/when-count-isnt-the-only-way-to-count/
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Great. Powerful query. It is simple and easy to understand, too. Many thanks for your help. I really appreciate it.
0 Likes 0 ·