D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.


My alternative to scraps of paper lying about.








Tuesday, 4 January 2011

Using the OVER clause - SQL

--
--using OVER clause to get count of SubTypes
--
SELECT E.EstablishmentName, S.JobSubTypeDescription
,COUNT(*) OVER(PARTITION BY S.JobSubTypeDescription) AS nums
FROM Establishment AS E LEFT OUTER JOIN
JobLog AS J ON E.EstablishmentID=J.JobLog_EstablishmentID INNER JOIN
JobSubType AS S ON J.JobLog_JobSubTypeID=S.JobSubTypeID
ORDER BY E.EstablishmentName

No comments: