<![CDATA[Simple database tricks]]>I’ve been keeping an inventory of my personal library in a database for more than a decade. It was thus easy, when writing the previous post, to find out the exact number of books I had bought from SFBC. I used the following SQL query:

SELECT EXTRACT(YEAR FROM purchase_date), COUNT(DISTNCT title)
    FROM inventory
    WHERE purchased_from = 'SFBC'
    GROUP BY EXTRACT(YEAR FROM purchase_date)

The results looked like this:

 date_part | count 
-----------+-------
      2001 |     1
      2002 |    11
      2003 |    24
      2004 |    18
      2005 |    24
      2006 |     8
      2007 |    10
      2008 |     6
      2009 |     4
      2010 |     5
      2011 |     5
      2012 |     9
      2013 |     3
           |   106
(14 rows)

I might ordinarily have used an ORDER BY clause to force the results into a useful order, but in this case the query planner decided to implement the GROUP BY using a sort so I didn’t need to. The last row in the results shows all the books I bought before I started the inventory database in 2001; it corresponds to books whose purchase_date is NULL because I didn’t have any record of when they were purchased.

]]>