Solved

SQL Query to count a set of unique records

Posted on 2007-12-04
3
153 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
[X]
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
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL page split per second is high 19 144
Can Unique column have more than one Null? 8 66
Please help for the below sql query. 1 34
Need to find substring in SQL 5 52
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…
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.

739 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