MySQL - how to use having in group by (SQL query) Have a query like this I use to find the number of products that match a number of words in a list of words:

SELECT GROUP_CONCAT(DISTINCT w.word) AS words, COUNT(DISTINCT w.word) AS wordcount, 
COUNT(*) AS productcount
FROM productwords pw JOIN words w ON pw.wordid = w.id 
WHERE w.word IN ('lenovo', 'laptop', 'computer') 
GROUP BY w.word HAVING COUNT(DISTINCT w.word) &gt;= 1


The query works like it should when matching 1 word, but for some reason it doesn't return matches of 2+ words.

i.e. the output is like this even though there are lots of products that matches 2+ words:

lenovo;1;320
laptop;1;7900
computer;1;8200


Any ideas for fixing this, so it will also return the 2+ word matches?

i.e. so it returns something like this:

lenovo;1;320
laptop;1;7900
computer;1;8200
lenovo laptop;2;278
lenovo computer;2;302
laptop computer;2;3200
etc.


Tables are like this:

products (id, name)
[1..*]
productwords (id, productid, wordid)
[*..1]
words (id, word)
The query has GROUP BY w.word so the COUNT(DISTINCT w.word) is - has to be - 1 for every group, so for every row in the results.

To get the results you want, you need to first GROUP BY product and then GROUP BY words:

SELECT g.words, 
       g.wordcount, 
       COUNT(*) AS productcount
FROM
    ( SELECT GROUP_CONCAT(DISTINCT w.word) AS words, 
             COUNT(DISTINCT w.word) AS wordcount
      FROM productwords pw JOIN words w ON pw.wordid = w.id 
      WHERE w.word IN ('lenovo', 'laptop', 'computer') 
      GROUP BY pw.productid
      -- HAVING COUNT(DISTINCT w.word) &gt;= 2
    ) AS g
GROUP BY g.words, g.wordcount ;