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

Question

I'm trying to create a mysql query to view the table where the search query is stored. This query attempts to get all rows matching the following criteria:

• >= To a date
• Return only rows where the search query appears 3 or more times
• Also only returns rows where 2 or more users have used the search query

This query doesn't work but should outline what I'm 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;

Sample data

id	user	Inquire	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 are brothers	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;

Using the values ​​set in the query above, a single row should be returned for the query "What is a dogs", all other columns are unimportant.

I know you can GROUP BY separate columns with commas, but I can't seem to figure out how to set a different value 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;