SELECT agegroups.lower,agegroups.upper,COUNT(answers.answer),SUM(IF(answers.answer='Ja',1,0)) FROM agegroups,answers,voters WHERE answers.voter_uid=voters.uid AND YEAR(NOW())-voters.birthyear BETWEEN agegroups.lower AND agegroups.upper GROUP BY agegroups.lower,agegroups.upper;
eller
SELECT agegroups.lower,agegroups.upper,IFNULL(x.total,0),IFNULL(x.yes,0) FROM agegroups LEFT JOIN (SELECT agegroups.lower AS lower,agegroups.upper AS upper,COUNT(answers.answer) AS total,SUM(IF(answers.answer='Ja',1,0)) AS yes FROM agegroups,answers,voters WHERE answers.voter_uid=voters.uid AND YEAR(NOW())-voters.birthyear BETWEEN agegroups.lower AND agegroups.upper GROUP BY agegroups.lower,agegroups.upper) x ON agegroups.lower=x.lower;
mysql> mysql> SELECT agegroups.lower,agegroups.upper,COUNT(answers.answer),SUM(IF(answe rs.answer='Ja',1,0)) -> FROM agegroups,answers,voters -> WHERE answers.voter_uid=voters.uid AND YEAR(NOW())-voters.birthyear BETWE EN agegroups.lower AND agegroups.upper -> GROUP BY agegroups.lower,agegroups.upper; +-------+-------+-----------------------+----------------------------------+ | lower | upper | COUNT(answers.answer) | SUM(IF(answers.answer='Ja',1,0)) | +-------+-------+-----------------------+----------------------------------+ | 20 | 29 | 3 | 3 | | 40 | 49 | 3 | 1 | | 50 | 59 | 3 | 2 | +-------+-------+-----------------------+----------------------------------+ 3 rows in set (0.00 sec)
mysql> mysql> SELECT agegroups.lower,agegroups.upper,IFNULL(x.total,0),IFNULL(x.yes,0) -> FROM agegroups LEFT JOIN (SELECT agegroups.lower AS lower,agegroups.upper AS upper,COUNT(answers.answer) AS total,SUM(IF(answers.answer='Ja',1,0)) AS yes
-> FROM agegroups,answers,voters -> WHERE answers.voter_uid=voters.uid AND YEAR(NOW())-voters.birthyear BETWE EN agegroups.lower AND agegroups.upper -> GROUP BY agegroups.lower,agegroups.upper) x ON agegroups.lower=x.lower; +-------+-------+-------------------+-----------------+ | lower | upper | IFNULL(x.total,0) | IFNULL(x.yes,0) | +-------+-------+-------------------+-----------------+ | 0 | 12 | 0 | 0 | | 13 | 19 | 0 | 0 | | 20 | 29 | 3 | 3 | | 30 | 39 | 0 | 0 | | 40 | 49 | 3 | 1 | | 50 | 59 | 3 | 2 | | 60 | 200 | 0 | 0 | +-------+-------+-------------------+-----------------+ 7 rows in set (0.00 sec)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.