CODEWITHSUNDEEP
mysqlgroup-bycounthaving
Adam B
Adam B

Reputation: 135

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

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:

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.

Upvotes: 2

Views: 853

Answers (1)

forpas
forpas

Reputation: 164099

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;

Upvotes: 2

Related Questions