Sql Joins issue

[Tr4c3yy]

Hi
I am running a query from the tables below and as well as needing to count all the 'pu.Unit_Instance_codes' (enrolments) I need to count the distinct 'UNIT_INSTANCES' (courses) but no matter what joins I use I am only getting the number of 'UNIT_INSTANCES' (courses) where there are enrolments and not the courses without.

Count(Distinct pu.UNIT_INSTANCE_CODE) Courses
,count(pu.PERSON_CODE) Total_Enrolments

FROM

PEOPLE p
INNER JOIN PEOPLE_UNITS pu
ON p.PERSON_CODE = pu.PERSON_CODE
Right JOIN UNIT_INSTANCE_OCCURRENCES uio
ON pu.UIO_ID = uio.UIO_ID
RIGHT JOIN UNIT_INSTANCES ui
ON pu.UNIT_INSTANCE_CODE = ui.FES_UNIT_INSTANCE_CODE

 

[Minty]

Right Joins are seldom if ever required, as they effectively are the same as a Left join, just with the precedence based on the Join order.

It's quite hard to see what you are trying to achieve without some sample data and your expected results, but suspect you really want to use a
window function COUNT() OVER

COUNT OVER PARTITION BY: An Explanation with 3 Examples

The combination of COUNT, OVER, and PARTITION BY is a powerful SQL syntax that is not very well known. Master it in just 10 minutes!

learnsql.com

COUNT(Distinct) info here:

SQL COUNT DISTINCT and SQL COUNT Examples in SQL Server

This tip shows several examples of COUNT and COUNT DISTINCT to show how SQL Server handles them differently.

www.mssqltips.com

 

[Tr4c3yy]

Thank you Minty.

The Sql is counting the total number of learners on all courses ( a monthly check to see how many enrolments in total and how many courses we have), the enrolments are fine but the course count only totals the number of courses with enrolments, I need it to include the courses without enrolments too.

 

[Minty]

The easiest route to do this is to take what yon need the All from (The courses) and then left then left join it bac to the people/courses query, something like (I cant work out your table names, it looks like you are missing a courses table? ;

SELECT  Course.*, CourseCount.CCount
FROM  Courses
Left Join

( UNIT_INSTANCE_CODE, Count(UNIT_INSTANCE_CODE) as CCount
  FROM UNIT_INSTANCE_CODE as Cu

  JOIN UNIT_INSTANCE_OCCURRENCES uio

ON pu.UIO_ID = uio.UIO_ID

JOIN PEOPLE_UNITS pu

ON p.PERSON_CODE = pu.PERSON_CODE
GROUP BY UNIT_INSTANCE_CODE
) as CourseCount on CourseCount.CourseCode = Courses.CourseCode

 

[Isaac]

start by formatting the sql in a way people can be helped to understand, as Minty said, eliminate the right join or explain what you want to do in simple terms with the tables and keys. it's hard to understand what you're after by whats' been posted.

 

[MadPiet]

Any chance you could post CREATE TABLE and INSERT scripts with dummy data so we can try out some solutions? You like tested answers, right?

Thank you Minty.

The Sql is counting the total number of learners on all courses ( a monthly check to see how many enrolments in total and how many courses we have), the enrolments are fine but the course count only totals the number of courses with enrolments, I need it to include the courses without enrolments too.

SELECT c.CourseID, COUNT(e.*)
FROM Courses c LEFT JOIN Enrollments e
ON c.CourseID = e.EnrollmentID