I don't know what you're trying to count here, but a
timestamp will have
zero effect on the grouping.
SELECT UserID, COUNT(QuestionID) AS QuestionsAnswered
FROM dbo.Data
WHERE DateTimeStamp BETWEEN @StartDate AND @EndDate
GROUP BY UserID
Now you know how many questions each user answered, and from
the record
count, know how many unique users answered questions. Summing
QuestionsAnswered in your application will show you total
questions
answered. Adding a single column gets you most of what you
want:
SELECT UserID, COUNT(QuestionID) AS QuestionsAnswered,
SUM(Answer) AS
YesAnswers
FROM dbo.Data
WHERE DateTimeStamp BETWEEN @StartDate AND @EndDate
GROUP BY UserID
Now, your application knows the number of unique users
answering questions
(from the row count), the total number of questions answered
(sum of
QuestionsAnswered), the number of "yes" (sum of YesAnswers)
and "no" (sum of
QuestionsAnswered - sum of YesAnswers) answers (you also know
this now per
user), and the percent of yes and no answers per user and in
total by doing
appropriate math with YesAnswers and QuestionsAnswered.
As for topics, there's really no difference to the above
query. Join in
your additional tables, change your grouping, and you can get
all the same
information.
Easy, no?