CODEWITHSUNDEEP
sqlsql-serversql-server-2005pivot
milo2011
milo2011

Reputation: 339

SQL Server 2005 GROUP BY and COUNT query for each month

I have a SQL Server 2005 table named Rentals:

RentalID
Book
Date

I want to return, using a query, for each book, how many rentals were in each month for a given year.

The results should look something like this:

+--------------------------------+-----+-----+-----+  
|              Book              | Jan | Feb | Mar |
+--------------------------------+-----+-----+-----+  
| Isaac Asimov - Foundation      |   2 |   5 |   3 |  
| H.G. Wells - War of the Worlds |   4 |   3 |   1 |  
| Frank Herbert - Dune           |   7 |   4 |   6 |
+--------------------------------+-----+-----+-----+

My query so far:

SELECT
Book, 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=1 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=2 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=3 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=4 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=5 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=6 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=7 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=8 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=9 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=10 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=11 AND year(Date)=2011), 
(SELECT COUNT(*) FROM Rentals WHERE month(Date)=12 AND year(Date)=2011) 
FROM Rentals 
GROUP BY Book

Upvotes: 2

Views: 346

Answers (2)

Stephen Bodine
Stephen Bodine

Reputation: 519

If you use pivot the code is much easier to maintain, 

SELECT 
    BOOK,
    [1] as Jan ,
    [2] as Feb,
    [3] as Mar,
    [4] as Apr,
    [5] as May,
    [6] as Jun,
    [7] as Jul,
    [8] as Aug,
    [9] as Sep,
    [10] as Oct,
    [11] as Nov,
    [12] as Dec 
FROM
(
    SELECT 
       BOOK , 
       DATEPART(MONTH,[DATE]) AS PER 
    FROM 
        Rentals 
    WHERE 
        DATEPART(YEAR,[DATE]) = 2014
) AS P PIVOT 
    (
    COUNT(PER) FOR PER IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS DATA

Simple.

Upvotes: 0

Taryn
Taryn

Reputation: 247640

This can be written much simpler by using a CASE expression inside of an aggregate function. This process is called PIVOT:

select book,
  sum(case when month(Date) = 1 then 1 else 0 end) Jan,
  sum(case when month(Date) = 2 then 1 else 0 end) Feb,
  sum(case when month(Date) = 3 then 1 else 0 end) Mar,
  sum(case when month(Date) = 4 then 1 else 0 end) Apr,
  sum(case when month(Date) = 5 then 1 else 0 end) May,
  sum(case when month(Date) = 6 then 1 else 0 end) Jun,
  sum(case when month(Date) = 7 then 1 else 0 end) Jul,
  sum(case when month(Date) = 8 then 1 else 0 end) Aug,
  sum(case when month(Date) = 9 then 1 else 0 end) Sep,
  sum(case when month(Date) = 10 then 1 else 0 end) Oct,
  sum(case when month(Date) = 11 then 1 else 0 end) Nov,
  sum(case when month(Date) = 12 then 1 else 0 end) Dec
from Rentals
where year(date) = 2011
group by book;

See SQL Fiddle with Demo. Instead of querying the table multiple times for each column, you use conditional aggregation to get the count for each book during the month and year.

Upvotes: 3

Related Questions