I have this query at the moment:
$sql = "SELECT
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2000’ AND prints.sale_price > 0) as year1,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2001’ AND prints.sale_price > 0) as year2,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2002’ AND prints.sale_price > 0) as year3,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2003’ AND prints.sale_price > 0) as year4,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2004’ AND prints.sale_price > 0) as year5,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2005’ AND prints.sale_price > 0) as year6,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2006’ AND prints.sale_price > 0) as year7,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2007’ AND prints.sale_price > 0) as year8,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2008’ AND prints.sale_price > 0) as year9,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2009’ AND prints.sale_price > 0) as year10,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2010’ AND prints.sale_price > 0) as year11,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ‘2011’ AND prints.sale_price > 0) as year12
FROM prints
GROUP BY year1, year2, year3, year4 ";
I’d like generalize the query by changing the year to current year -5, current year -4… current year, current year +1 etc…
What’s the suggestions for doing this?
Thanks
David