SQL 117 - SQL SERVER - GROUP BY and COUNT function
englishspacedog YouTube tutorial from Canada
http://www.herostratus.ca/PC.htm
How to use the GROUP BY construct and the COUNT function
SELECT tblGALAXIES.GalaxyID,
tblGALAXIES.GalaxyName,
tblSTARS.StarID,
tblSTARS.StarName,
tblPLANETS.PlanetID,
tblPLANETS.PlanetName,
rtrim(ltrim(tblPLANETS.PlanetName))
' ' + tblSTARS.StarName AS PlanetFullName,
tblPLANETS.PlanetWeight
/(SELECT PlanetWeight FROM tblPLANETS WHERE PlanetID = 3)
AS WeightInEarths
FROM tblGALAXIES
LEFT JOIN tblSTARS
ON tblGALAXIES.GalaxyID = tblSTARS.GalaxyID
LEFT JOIN tblPLANETS
ON tblSTARS.StarID = tblPLANETS.StarID
ORDER BY tblGALAXIES.GalaxyName,
tblSTARS.StarName,
tblPLANETS.PlanetName;
SELECT tblPLANETS.PlanetName,
COUNT(*) as PlanetCount
FROM tblPLANETS
GROUP BY PlanetName;