Solved

SQL Query to count a set of unique records

Posted on 2007-12-04
3
149 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 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: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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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