Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Displaying Data by Time Units

How do you get statistics on event per time unit? GROUP BY is the answer to this interesting problem. For example, what is the distribution by month of books published during the year? Check your vendor for functions similar to DATEPART, which displays a specified date part, such as month or year, for a complete date. The results show a cluster of books published during the sixth month:

SQL

select datepart(month, pubdate), count(title_id)
from titles
group by datepart(month, pubdate)
datepart(month,titles.pubdate count(titles.title_id)
============================= ======================
                            6                     13
                           10                      3
                       (NULL)                      2
[3 rows]

For readability, add column names as in the following query:

SQL

 select datepart(month, pubdate)  ...