CODEWITHSUNDEEP
mysqldatabasegroup-by
frosty
frosty

Reputation: 2659

How to use GROUP BY together with HAVING?

I'm trying to select all ips from my database that has visited my website, more than once, at least a day apart, from this month, but my sql query is showing an error. What am I doing wrong?

SELECT ip,COUNT(ip) AS matches
FROM ipAll
WHERE DATE LIKE  '%-12-%'
HAVING matches > '1'
GROUP BY ip

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY ip

Upvotes: 0

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

HAVING comes after GROUP BY:

SELECT ip, COUNT(ip) AS matches
FROM ipAll
WHERE DATE LIKE  '%-12-%'
GROUP BY ip
HAVING matches > 1;

However, you should write the query more like this:

SELECT ip, COUNT(ip) AS matches
FROM ipAll
WHERE MONTH(DATE) = 12
GROUP BY ip
HAVING matches > 1;

Do not use LIKE on dates, because the date is converted to a string (implicitly) using local internationalization settings. These could change and just break the code. Also, do not use string constants where you want a number.

Upvotes: 5

Gaurav Lad
Gaurav Lad

Reputation: 1808

SELECT ip, COUNT(ip) AS matches
FROM ipAll
WHERE DATE LIKE  '%-12-%'
GROUP BY ip
HAVING matches > 1;

Upvotes: 1

Naruto
Naruto

Reputation: 4329

The error is because according to mysql documentation having clause must be after group by clause not before.

So your query must look like this

SELECT ip,COUNT(ip) AS matches FROM ipAll WHERE DATE LIKE  '%-12-%' GROUP BY ip HAVING matches > '1'

Upvotes: 2

Related Questions