MySQL date query only returns one year, when multiple exist

Question

I'm a part-time designer/developer with a part-time photography business. I've got a database of photos with various bits of metadata attached. I want to query the database and return a list of the years that photos were taken, and the quantity of photos that were taken in that year.

In short, I want a list that looks like this:

2010 (35 photos)
2009 (67 photos)
2008 (48 photos)

Here's the query I'm using:

SELECT YEAR(date) AS year, COUNT(filename) as quantity FROM photos WHERE visible='1' GROUP BY 'year' ORDER BY 'year' DESC

Instead of churning out all the possible years (the database includes photos from 2010-2008), this is the sole result:

2010 (35 photos)

I've tried a lot of different syntax but at this point I'm giving in and asking for help!

Answer 1

Don't single-quote year.
That is telling MySQL that year is a string; which it isn't.

In addition, don't use an alias in the GROUP BY.

Try this:

SELECT YEAR(date) AS year, COUNT(filename) as quantity 
FROM photos 
WHERE visible='1' 
GROUP BY YEAR(date)
ORDER BY YEAR(date) DESC

Edit:
In most RDBMSes the SELECT clause is interpreted last.
So we cannot use a column alias in other clauses of that particular query.

Answer 2

T-SQL Solution:

SELECT 
   [text] = 
      cast(year(date) as varchar) + ' ('+cast(count(filename) as varchar) + 
      'photo'+case when count(filename)>1 then 's' else '' end + ')'
   ,
   [year] = year(date)
FROM photos 
WHERE  visible='1' 
GROUP BY YEAR(date)
ORDER BY YEAR(date) DESC