Sqlite count occurence per year

Question

So let's say I have a table in my Sqlite database with some information about some files, with the following structure:

|  id  |   file format  |          creation date           |
----------------------------------------------------------
|   1  |      Word      |    2010:02:12 13:31:33+01:00     |
|   2  |      PSD       |    2021:02:23 15:44:51+01:00     |
|   3  |      Word      |    2019:02:13 14:18:11+01:00     |
|   4  |      Word      |    2010:02:12 13:31:20+01:00     |
|   5  |      Word      |    2003:05:25 18:55:10+02:00     |
|   6  |      PSD       |    2014:07:20 20:55:58+02:00     |
|   7  |      Word      |    2014:07:20 21:09:24+02:00     |
|   8  |      TIFF      |    2011:03:30 11:56:56+02:00     |
|   9  |      PSD       |    2015:07:15 14:34:36+02:00     |
|  10  |      PSD       |    2009:08:29 11:25:57+02:00     |
|  11  |      Word      |    2003:05:25 20:06:18+02:00     |

I would like results that show me a chronology of how many of each file format were created in a given year – something along the lines of this:

 |Format| 2003 | 2009 | 2010 | 2011 | 2014 | 2015 | 2019 | 2021 |    
 ----------------------------------------------------------------
 | Word |   2  |  0   |   0  |   2  |  0   |   0  |   2  |  0   |    
 | PSD  |   0  |  1   |   0  |   0  |  1   |   1  |   0  |  1   |
 | TIFF |   0  |  0   |   0  |   1  |  0   |   0  |   1  |  0   |

I've gotten kinda close (I think) with this, but am stuck:

SELECT 
    file_format, 
    COUNT(CASE file_format WHEN creation_date LIKE '%2010%' THEN 1 ELSE 0 END),
    COUNT(CASE file_format WHEN creation_date LIKE '%2011%' THEN 1 ELSE 0 END),
    COUNT(CASE file_format WHEN creation_date LIKE '%2012%' THEN 1 ELSE 0 END)
FROM 
    fileinfo 
GROUP BY
    file_format;

When I do this I am getting unique amounts for each file format, but the same count for every year…

 |Format| 2010 | 2011 | 2012 |  
 -----------------------------
 | Word |   4  |  4   |   4  |    
 | PSD  |   1  |  1   |   1  |  
 | TIFF |   6  |  6   |   6  | 

Why am I getting that incorrect tally, and moreover, is there a smarter way of querying that doesn't rely on the year being statically searched for as a string for every single year? If it helps, the column headers and row headers could be switched – doesn't matter to me. Please help a n00b :(

Answer 1

Use SUM() aggregate function for conditional aggregation:

SELECT file_format, 
       SUM(creation_date LIKE '2010%') AS `2010`,
       SUM(creation_date LIKE '2011%') AS `2011`,
       ..........................................
FROM fileinfo 
GROUP BY file_format;

See the demo.