How to GROUP BY multiple columns with multiple HAVING values in MySQL?

Question

I am trying to create a mysql query that looks at a table that stores search queries. The query is trying to get all rows that:

• >= to a date
• Only returning rows where the search query has 3 or more occurrences
• Also only returning rows where 2 or more users have used a search query

This query does not work, but should outline what Im trying to do:

SELECT 
  * 
FROM 
  `analytics` 
WHERE 
  `date` >= '2021-01-01' 
GROUP BY 
  `query` 
HAVING 
  COUNT(*) >= 3 
  AND 
GROUP BY 
  `user` 
HAVING 
  COUNT(*) >= 2 
ORDER BY 
  id DESC;

Example Data

id	user	query	date
1	5	What is a dog	2021-01-01
2	5	What is a dog	2021-01-01
3	6	What is a dog	2021-01-01
4	7	What is a dog	2021-01-01
5	7	What is a brog	2021-01-01

Example SQL

SELECT 
  * 
FROM 
  analytics 
WHERE 
  date >= '2021-01-01' 
GROUP BY 
  query 
HAVING 
  COUNT(*) >= 3 
  AND 
GROUP BY 
  user 
HAVING 
  COUNT(*) >= 2 
ORDER BY 
  id DESC;

With the values set in the query above, a single row should return with the query "What is a dog", all other columns don't really matter.

I know you can comma separate columns to GROUP BY but I can't seem to figure out how to have different values for each column.

Answer 1

You can set both conditions in the same HAVING clause:

SELECT `query` 
FROM `analytics` 
WHERE `date` >= '2021-01-01' 
GROUP BY `query`
HAVING COUNT(*) >= 3 AND COUNT(DISTINCT `user`) >= 2;