In this blog shared SQL queries with explanation, different syntax and use cases.
1 -- show the number of employees who started in the same month as the employee in the row.
We will need to narrow or restrict the count to just that month for each row. We use the window
We will need to narrow or restrict the count to just that month for each row. We use the window
PARTITION clause, like so:SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;
Partitions allow you to filter the window into sections by a certain value or values. Each section is often called the window frame.
2 -- let’s say we not only wanted to find out how many
employees started in the same month, but we want to show
in which order they started that month.
SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started)
ORDER BY date_started) As NumThisMonth,
DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth,
firstname, lastname, date_started
FROM Employee
ORDER BY date_started;
In this case, ORDER BY modifies the window so that it goes from the start of the partition (in this case the month and year of when the employee started) to the current row.
Thus, the count restarts at each partition.
