Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query to count a set of unique records

Posted on 2007-12-04
3
Medium Priority
?
165 Views
Last Modified: 2010-03-19
I am working with the following query:

SELECT     XYZQuiz.Name AS 'Course', COUNT(*) AS 'Total Courses Completed'
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId
WHERE     need some help here
GROUP BY XYZQuiz.Name

My objective is to get the Total Number of courses completed (only reporting each course once per userID).

Would appreciate any help at all.

TIA!
0
Comment
Question by:dstjohnjr
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 2000 total points
ID: 20407948
try something like this.

SELECT DISTINCT myTable.[Course], myTable.[Total Courses Completed]
From
(
SELECT     XYZQuiz.Name AS 'Course', COUNT(*) AS 'Total Courses Completed'
FROM         XYZQuiz INNER JOIN
                      XYZQuizUserLog ON XYZQuiz.QuizID = XYZQuizUserLog.QuizID INNER JOIN
                      XYZUser ON XYZQuizUserLog.UserID = XYZUser.UserId
GROUP BY XYZQuiz.Name
) as myTable
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20407961
Ignore the last entry as I missed the userid part of your requirements.
0
 

Author Comment

by:dstjohnjr
ID: 20408026
That helps immensely.  Thanks!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

782 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