May 192014
So there I was, contemplating whether I could produce a nice and simple bar chart showing the number of films I’ve watched per month. Using just MySQL.
I knew it was easy to produce a simple numerical count with count(*) and a group by clause, but a bar chart? Turns out it was easy with the repeat string function.
mysql> select date_format(whence, "%Y-%m") "Month",
repeat('*', count(*)) "Number of Films"
from films group by date_format(whence, "%Y-%m");
+---------+------------------------------------------------+
| Month | Number of Films |
+---------+------------------------------------------------+
| 2007-05 | **** |
| 2007-06 | ****** |
| 2007-07 | ****** |
| 2007-08 | ***** |
| 2007-09 | ****** |
| 2007-10 | *** |
| 2007-12 | **** |
| 2008-01 | ***** |
| 2008-02 | **** |
| 2008-03 | * |
| 2008-04 | ***** |
| 2008-05 | ***** |
| 2008-06 | ****** |
| 2008-07 | **** |
| 2008-08 | ***** |
| 2008-09 | ***** |
| 2008-10 | **** |
| 2008-11 | ***** |
| 2008-12 | *** |
| 2009-01 | *** |
| 2009-03 | ** |
| 2009-04 | *** |
| 2009-05 | ** |
| 2009-06 | **** |
| 2009-07 | **** |
| 2009-08 | * |
| 2009-09 | ** |
| 2009-10 | **** |
| 2009-11 | ** |
| 2009-12 | ** |
| 2010-01 | ********** |
| 2010-02 | ********** |
| 2010-03 | ************************* |
| 2010-04 | ***************************** |
| 2010-05 | ********************************************** |
| 2010-06 | *********************** |
| 2010-07 | **************** |
| 2010-08 | ********** |
| 2010-09 | ************ |
| 2010-10 | ********** |
| 2010-11 | ******** |
| 2010-12 | ********* |
| 2011-01 | ******************* |
| 2011-02 | ************* |
| 2011-03 | ** |
| 2011-04 | ************ |
| 2011-05 | ******** |
| 2011-06 | *** |
| 2011-07 | **** |
| 2011-08 | ********************* |
| 2011-09 | * |
| 2011-10 | ** |
| 2011-11 | *************** |
| 2011-12 | ******************** |
| 2012-01 | ******************** |
| 2012-02 | ******* |
| 2012-03 | ******* |
| 2012-04 | ***** |
| 2012-05 | ****** |
| 2012-06 | ******* |
| 2012-07 | ************** |
| 2012-08 | ************ |
| 2012-09 | *************** |
| 2012-10 | ******************* |
| 2012-11 | **************** |
| 2012-12 | ******* |
| 2013-01 | ************* |
| 2013-02 | ************* |
| 2013-03 | ****************** |
| 2013-04 | ****************** |
| 2013-05 | ******** |
| 2013-06 | ************ |
| 2013-07 | ************** |
| 2013-08 | *** |
| 2013-09 | ************* |
| 2013-10 | ******** |
| 2013-11 | *************** |
| 2013-12 | *********************** |
| 2014-01 | ************************* |
| 2014-02 | ******** |
| 2014-03 | ************* |
| 2014-04 | **************** |
| 2014-05 | ************ |
+---------+------------------------------------------------+
83 rows in set (0.02 sec)