Man of Distinct(ion)

Its not often that I learn something new about the fundamentals of SQL. Sure they add to the syntax all the time and you learn something  about what you can do with these addition but fundamentals, that’s basic stuff – the kind of stuff you rattle off a thousand times a day without even thinking about it . I’m talking about  stuff I should have known though it seems that I am not alone so I thought I would share this tidbit. This knowledge  is to do with the DISTINCT keyword of all things.

If I run the following piece of SQL I get a large no of results, covering all of the intakes available across all courses for all campuses:-

SELECT  C.[Description],O.Description,O.TotalIntake FROM Campus AS C  INNER JOIN Intake AS I
    INNER JOIN Courses AS O ON I.IntakeID = O.Id
    ON C.CampusID=I.CampusID

The results look a little like this:-

But what if I want to return a count of all of the course types at a campus and the number of seats available over the whole intake.The obvious thing is group by campus and then count the number of Courses like this

SELECT C.[Description],COUNT(O.Description),SUM(O.TotalIntake) FROM Campus AS C  INNER JOIN Intake AS I
    INNER JOIN Courses AS O ON I.IntakeID = O.Id
    ON C.CampusID=I.CampusID
GROUP BY C.[Description]

Wait a minute, that’s not right though is it. I don’t have  84 different course types at my Shrewsbury Campus. Turns out that  getting a straight count of the course types is not quite as simple as that , that is unless you know what I now know…

That DISTINCT may be used within a COUNT function.

Its obvious, sure but I really had no idea that this was supported by SQL. So I refactor the SQL to look like this

SELECT C.[Description],COUNT(DISTINCT O.Description),SUM(O.TotalIntake) FROM Campus AS C  INNER JOIN Intake AS I
    INNER JOIN Courses AS O ON I.IntakeID = O.Id
    ON C.CampusID=I.CampusID
GROUP BY C.[Description]

 

And there it is, We have distinct course types at each Campus. I guess I’ve probably really over engineered solutions  in the past because I never knew about this little nugget of amazingness. Go forth and be awesome.