Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Query to count a set of unique records

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select * from 6 43
Upgrading SQL 2005 Express to 2008 R2 Express 31 124
Need help with a query 6 75
Need help how to find where my error is in UFD 6 40
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

789 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