Solved

SQL Query to count a set of unique records

Posted on 2007-12-04
3
146 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:
xDJR1875 earned 500 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:xDJR1875
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now