PHP/MySQL - SELECT and SUM Distinct GROUP BY ColumnA WHERE columnB = ? AND Column C = ?. What is the-CodePudding

Consider this table:

 ------------- -------------- ----------- ------------ ---------------- -------------------- 
| Chapter(INT)| Unit (INT)   | Item (INT)| WordNo(INT)| Word (VCHAR)   |PartofSpeech(VCHAR) |
 ------------- -------------- ----------- ------------ ---------------- -------------------- 
| 4           | 1            | 1         | 1          | He             | pronoun            |
| 4           | 1            | 1         | 2          | sells          | verb               |
| 4           | 1            | 1         | 3          | Apples         | noun               |
| 4           | 1            | 2         | 1          | Those          | pronoun            |
| 4           | 1            | 2         | 2          | apples         | noun               |
| 4           | 1            | 2         | 3          | are            | verb               |
| 4           | 1            | 2         | 4          | red            | adjective          |
| 4           | 1            | 3         | 1          | Green          | adjective          |
| 4           | 1            | 3         | 2          | grapes         | noun               |
| 4           | 1            | 3         | 3          | are            | verb               |
| 4           | 1            | 3         | 4          | delicious      | adjective          |
| 4           | 1            | 4         | 1          | These          | pronoun            |
| 4           | 1            | 4         | 2          | apples         | noun               |
| 4           | 1            | 4         | 3          | are            | verb               |
| 4           | 1            | 4         | 4          | rotten         | adjective          |
 ------------------------------------------------------------------------------

Now, say I want to be able to select each DISTINCT group of the 'Item' column WHERE Word = 'apple' AND PartofSpeech = 'adjective' and sum them. So based on this table, sum should be 2, and I should return the sentences for Item 2 ("Those apples are red.") and Item 4 ("These apples are rotten.").

I had assumed this query would work, but it seems I need to GROUP BY before I query, right?

"SELECT SUM(itemnum) AS totalitems
FROM (SELECT COUNT(DISTINCT Item) AS itemnum 
FROM EnglishLevel1
WHERE Word = 'apples'
AND PartofSpeech = 'adjective'
GROUP BY Chapter, Unit, Item
) t"

I have also looked into the "IN" operator as well as changed AND to OR, but that doesn't get the desired result. Perhaps a subquery is required?

CodePudding user response：

You may use aggregation as follows:

SELECT Chapter, Unit, Item,
       GROUP_CONCAT(Word ORDER BY WordNo SEPARATOR ' ') AS sentence
FROM EnglishLevel1
GROUP BY Chapter, Unit, Item
HAVING SUM(Word = 'apple') > 0 AND SUM(PartofSpeech = 'adjective') > 0
ORDER BY Chapter, Unit, Item;