Group by question with example

[shebbycs]

There are 3 questions which are :

 

1. Group functions work across many rows to produce one result per group (My answer : Yes)

 

2. Group functions include Null in calculations (My answer : Yes and No)

 

3. There WHERE clause restricts rows prior to inclusion in a group calculations. (My answer : Yes)

 

But each of these questions need to give a example for every statement

 

Hope you can help me please 

 

Thank you

[Barand]

The data

+--------+-------+-----------+-------+
| idtest | name  | subject   | score |
+--------+-------+-----------+-------+
|      1 | Peter | English   |  NULL |
|      2 | Peter | Maths     |    84 |
|      3 | Peter | Chemistry |    66 |
|      4 | Mary  | English   |    90 |
|      5 | Mary  | Maths     |    80 |
|      6 | Mary  | Chemistry |    50 |
|      7 | Paul  | English   |    60 |
|      8 | Paul  | Maths     |     0 |
|      9 | Paul  | Chemistry |   100 |
+--------+-------+-----------+-------+

1. Find the total score for each student

mysql> SELECT name, SUM(score) as total
    -> FROM testdata
    -> GROUP BY name;
+-------+-------+
| name  | total |
+-------+-------+
| Mary  |   220 |
| Paul  |   160 |
| Peter |   150 |
+-------+-------+

2. Find the average score for each subject

mysql> SELECT subject, SUM(score) as total, COUNT(*) as num,
    -> COUNT(score) as numscores, AVG(score) as average
    -> FROM testdata
    -> GROUP BY subject;
+-----------+-------+-----+-----------+---------+
| subject   | total | num | numscores | average |
+-----------+-------+-----+-----------+---------+
| Chemistry |   216 |   3 |         3 | 72.0000 |
| English   |   150 |   3 |         2 | 75.0000 |  <-- null was ignored
| Maths     |   164 |   3 |         3 | 54.6667 |
+-----------+-------+-----+-----------+---------+

Note there was a NULL score for English which was ignored, (so the answer is NO they are not included in group aggregations)

 

3. Find the lowest non-zero score

mysql> SELECT MIN(score) as lowest
    -> FROM testdata
    -> WHERE score > 0;
+--------+
| lowest |
+--------+
|     50 |
+--------+

[shebbycs]

Thanks a lot barand