Gruppere aldersgruppe efter fødselsår og summere ja svar for disse?

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> CREATE TABLE answers (
    ->    uid INTEGER NOT NULL PRIMARY KEY,
    ->    question_uid INTEGER,
    ->    voter_uid INTEGER,
    ->    answer VARCHAR(3)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> CREATE TABLE agegroups (
    ->    uid INTEGER NOT NULL PRIMARY KEY,
    ->    lower INTEGER,
    ->    upper INTEGER
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO voters VALUES(1, 1961);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO voters VALUES(2, 1971);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO voters VALUES(3, 1983);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO answers VALUES(1, 1, 1, 'Ja');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(2, 2, 1, 'Ja');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(3, 3, 1, 'Nej');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(4, 1, 2, 'Ja');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(5, 2, 2, 'Nej');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(6, 3, 2, 'Nej');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(7, 1, 3, 'Ja');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(8, 2, 3, 'Ja');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO answers VALUES(9, 3, 3, 'Ja');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO agegroups VALUES(1, 0, 12);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO agegroups VALUES(2, 13, 19);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO agegroups VALUES(3, 20, 29);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO agegroups VALUES(4, 30, 39);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO agegroups VALUES(5, 40, 49);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO agegroups VALUES(6, 50, 59);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO agegroups VALUES(7, 60, 200);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM voters;
+-----+-----------+
| uid | birthyear |
+-----+-----------+
|  1 |      1961 |
|  2 |      1971 |
|  3 |      1983 |
+-----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM answers;
+-----+--------------+-----------+--------+
| uid | question_uid | voter_uid | answer |
+-----+--------------+-----------+--------+
|  1 |            1 |        1 | Ja    |
|  2 |            2 |        1 | Ja    |
|  3 |            3 |        1 | Nej    |
|  4 |            1 |        2 | Ja    |
|  5 |            2 |        2 | Nej    |
|  6 |            3 |        2 | Nej    |
|  7 |            1 |        3 | Ja    |
|  8 |            2 |        3 | Ja    |
|  9 |            3 |        3 | Ja    |
+-----+--------------+-----------+--------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM agegroups;
+-----+-------+-------+
| uid | lower | upper |
+-----+-------+-------+
|  1 |    0 |    12 |
|  2 |    13 |    19 |
|  3 |    20 |    29 |
|  4 |    30 |    39 |
|  5 |    40 |    49 |
|  6 |    50 |    59 |
|  7 |    60 |  200 |
+-----+-------+-------+
7 rows in set (0.00 sec)

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)

mysql>
mysql> DROP TABLE voters;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE answers;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE agegroups;
Query OK, 0 rows affected (0.00 sec)

Hej Arne.

Tusind tak for det meget udførlige svar som også fik lært mig en hel del i den forbindelse.

Du må gerne smide et svar, det er om noget velfortjent!

svar