PHP - Count distinct rows where two other columns are distinct-CodePudding

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:

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.)

CodePudding user response：

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;