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.

Please post table description, sample data and expected result in text format — Ergest Basha, Jan 26 '22 at 19:23
Please provide sample data and expected results as per [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Stu, Jan 26 '22 at 19:27

score 2 · Accepted Answer · answered Jan 26 '22 at 19:55

2

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;

answered Jan 26 '22 at 19:55

forpas

160,666
10
38
76

@ErgestBasha why not? – forpas Jan 26 '22 at 20:05
@ErgestBasha this is exactly why "What is a brog" should be returned: it occurs at least 3 times and for more than 2 users. I don't understand what you don't understand. – forpas Jan 26 '22 at 20:11
make it group by user and COUNT(Distinct 'query') >= 2 maybe?' Depends exactly what you want. – nathan hayfield Jan 26 '22 at 20:35
2

This works exactly as needed. @forpas is correct, in your example "What is a brog" should also come up because it has been searched 3 or more times and by 2 or more different users. Thanks! – Adam B Jan 26 '22 at 20:35

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

1 Answers1