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.