I'm trying to output the number of distinct results from a row where two other tables factor into the equation, but I'm not sure how to make this work. Here's what I have. I have counted the total times which a word appears in the database.
$total = "SELECT word, count(word) total FROM Table WHERE Word = 'Apples'";
total = 9. Ok, good. That was easy.
+-------------+--------------+--------------+--------------+
| BookID (INT)| chapter (INT)| page (INT) | word (VCHAR) |
+-------------+--------------+--------------+--------------+
| 40 | 1 | 8 | Apples |
| 40 | 1 | 8 | Apples |
| 40 | 1 | 15 | Apples |
| 40 | 4 | 23 | Apples |
| 50 | 3 | 15 | Apples |
| 50 | 6 | 15 | Apples |
| 51 | 13 | 1 | Apples |
| 52 | 2 | 3 | Apples |
| 60 | 8 | 1 | Apples |
+-------------------------------------------+--------------+
Now, here's where I'm needing some assistance. I want to find the number of times the word is used on a DISTINCT page in each chapter of each book. So, based on the table above, I am expecting the total to be 8. I'm close with this code, but I can't find the next step.
$pages = "SELECT COUNT(DISTINCT page) AS num FROM Table WHERE word = 'Apples' GROUP BY BookID, chapter, page";
This gives me:
1
1
1
1
1
1
1
1
So, I'm getting the correct number. 8 rows. Now I just need to add those up and output them as a single number. 8. I've looked into SUM, but that doesn't seem to work (if I'm mistaken, please show me how I should include it.)
You may simply take the sum of your current query as a subquery:
SELECT SUM(num) AS total
FROM
(
SELECT COUNT(DISTINCT page) AS num
FROM yourTable
WHERE word = 'Apples'
GROUP BY BookID, chapter, page
) t;