I am posting the entire mySQL call so you can see what I’m trying to do. There are three things wrong:
- It’s ugly,
- It’s really long, and
- it doesn’t work…
Other than that, it’s ok…
$sql = "SELECT
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 10) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard1 ,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 9) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as year2,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 8) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard3,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 7) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard4,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 6) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard5,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 5) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard6,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 4) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard7,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 3) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard8,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 2) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard9,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard10,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = $year AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard11,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year + 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard12,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 10) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare1 ,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 9) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare2,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 8) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare3,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 7) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare4,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 6) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare5,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 5) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare6,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 4) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare7,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 3) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare8,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 2) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare9,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year - 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare10,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = $year AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare11,
(SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = ($year + 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare12,
(SELECT yeard1 + (yeare1 * $euro) from prints as year1),
(SELECT yeard2 + (yeare2 * $euro) from prints as year2),
(SELECT yeard3 + (yeare3* $euro) from prints as year3),
(SELECT yeard4 + (yeare4 * $euro) from prints as year4),
(SELECT yeard5 + (yeare5 * $euro) from prints as year5),
(SELECT yeard6 + (yeare6 * $euro) from prints as year6),
(SELECT yeard7 + (yeare7 * $euro) from prints as year7),
(SELECT yeard8 + (yeare8 * $euro) from prints as year8),
(SELECT yeard9 + (yeare9 * $euro) from prints as year9),
(SELECT yeard10 + (yeare10 * $euro) from prints as year10),
(SELECT yeard11 + (yeare11 * $euro) from prints as year12),
(SELECT yeard12 + (yeare12 * $euro) from prints as year12)
FROM prints
GROUP BY year1, year2, year3, year4 ";
This is the code output:
Errors :
1) SELECT (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 10) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard1 , (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 9) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as year2, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 8) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard3, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 7) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard4, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 6) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard5, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 5) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard6, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 4) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard7, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 3) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard8, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 2) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard9, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard10, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = 2010 AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard11, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 + 1) AND prints.sale_price > 0 AND prints.currency = 'US Dollars') as yeard12, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 10) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare1 , (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 9) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare2, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 8) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare3, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 7) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare4, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 6) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare5, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 5) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare6, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 4) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare7, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 3) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare8, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 2) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare9, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 - 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare10, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = 2010 AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare11, (SELECT SUM(prints.print_key) FROM prints WHERE SUBSTR(prints.date_printed,1,4) = (2010 + 1) AND prints.sale_price > 0 AND prints.currency = 'Euros') as yeare12, (SELECT yeard1 + yeare1 from prints as year1), (SELECT yeard2 + yeare2 from prints as year2), (SELECT yeard3 + yeare3 from prints as year3), (SELECT yeard4 + yeare4 from prints as year4), (SELECT yeard5 + yeare5 from prints as year5), (SELECT yeard6 + yeare6 from prints as year6), (SELECT yeard7 + yeare7 from prints as year7), (SELECT yeard8 + yeare8 from prints as year8), (SELECT yeard9 + yeare9 from prints as year9), (SELECT yeard10 + yeare10 from prints as year10), (SELECT yeard11 + yeare11 from prints as year12), (SELECT yeard12 + yeare12 from prints as year12) FROM prints GROUP BY year1, year2, year3, year4 ORDER BY prints.date_printed DESC LIMIT 0, 10
[nativecode=1054 ** Unknown column 'yeard2' in 'field list']