ansaurus

Question

Answer 1

+1 A:

Use a "group by" query to get all your totals at once.

SELECT YEAR(date) as year, SUM(amountraised) AS year_total 
FROM fundraisingtotal 
GROUP BY YEAR(date)

SorcyCat 2010-01-27 20:22:39

Thanks. That gives me the totals for the year, but for the current year only (which I already have elsewhere on the page with a slightly different query (query2 in my original post). If I take out the `WHERE YEAR(date) = YEAR(CURDATE())` that gives me the total for each year (and a blank row in the HTML table with a year listed as 0 for some reason), but takes out the targets from the 2nd column of the HTML table.

NeonBlue Bliss 2010-01-28 10:32:34

D'oh! I should be more careful when you copy code. Corrected that in the answer. @dev-null-dweller's answer looked great, btw.

SorcyCat 2010-01-28 14:35:31

Answer 2

+1 A:

SELECT a.year , SUM( f.amountraised ) , a.target
FROM annualtarget a INNER JOIN  fundraisingtotal f ON (a.year = YEAR( f.date ))
GROUP BY a.year

dev-null-dweller 2010-01-27 20:27:08

Argggh! I'd forgotten about joins (never did get the hang of 'em!) - I think this is along the lines of what I need. I've tried running it in PHPMyAdmin, and it gives a total for 2009, and the target on the same row, but the total's way off (about £2000 less than I know was raised), and when I tried to use it on the web page, I couldn't figure out what I needed to use in the while loop, i.e. `$year_total = mysql_result($result,$i,"???");` so of course get MySQL errors displaying - `Warning: mysql_result() [function.mysql-result]: year_total not found in MySQL result index 7 in` ...etc

NeonBlue Bliss 2010-01-28 11:04:08

@NeonBlue Blis if you want to look for the year_total variable you need to rename the variable with the AS keyword as I did in my answer.

SorcyCat 2010-01-28 14:37:20

Thank you both so much - all working fine now! :-) This is my finished query:`SELECT a.year , SUM( f.amountraised ) AS year_total, a.targetFROM annualtarget a INNER JOIN fundraisingtotal f ON (a.year = YEAR( f.date ))GROUP BY a.year ORDER BY year DESC`

NeonBlue Bliss 2010-01-28 20:08:50

ansaurus

tags:

views:

answers:

Sum values by year with MySQL/PHP

related questions