Database Reference
In-Depth Information
mysql>
SELECT subject, A, B, score FROM t ORDER BY subject;
+---------+------+------+-------+
| subject | A | B | score |
+---------+------+------+-------+
| 1 | 1 | 1 | 18 |
| 2 | 1 | 1 | NULL |
| 3 | 1 | 1 | 23 |
| 4 | 1 | 1 | 24 |
| 5 | 1 | 2 | 17 |
| 6 | 1 | 2 | 23 |
| 7 | 1 | 2 | 29 |
| 8 | 1 | 2 | 32 |
| 9 | 2 | 1 | 17 |
| 10 | 2 | 1 | NULL |
| 11 | 2 | 1 | NULL |
| 12 | 2 | 1 | 25 |
| 13 | 2 | 2 | NULL |
| 14 | 2 | 2 | 33 |
| 15 | 2 | 2 | 34 |
| 16 | 2 | 2 | 37 |
+---------+------+------+-------+
To produce a summary for each combination of conditions, use a
GROUP
BY
clause:
mysql>
SELECT A, B, COUNT(*) AS 'n (total)',
->
COUNT(score) AS 'n (nonmissing)',
->
COUNT(*) - COUNT(score) AS 'n (missing)',
->
((COUNT(*) - COUNT(score)) * 100) / COUNT(*) AS '% missing'
->
FROM t
->
GROUP BY A, B;
+------+------+-----------+----------------+-------------+-----------+
| A | B | n (total) | n (nonmissing) | n (missing) | % missing |
+------+------+-----------+----------------+-------------+-----------+
| 1 | 1 | 4 | 3 | 1 | 25.0000 |
| 1 | 2 | 4 | 4 | 0 | 0.0000 |
| 2 | 1 | 4 | 2 | 2 | 50.0000 |
| 2 | 2 | 4 | 3 | 1 | 25.0000 |
+------+------+-----------+----------------+-------------+-----------+
15.5. Calculating Linear Regressions or Correlation
Coefficients
Problem
You want to calculate the least-squares regression line for two variables or the correlation
coefficient that expresses the strength of the relationship between them.
Solution
Apply summary functions to calculate the necessary terms.
