CODEWITHSUNDEEP
mysqlsql
aherlambang
aherlambang

Reputation: 14418

issue with sql query to count by groupby

I have the following query:

SELECT s.username FROM `instagram_shop` s
INNER JOIN `instagram_shop_picture` p
ON s.id = p.shop_id
WHERE s.`deletedAt` IS NULL
HAVING COUNT(p.id) = 0
GROUP BY s.id

and I keep getting the following 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 s.id LIMIT 0, 30' at line 6

What is the issue here?

Upvotes: 1

Views: 97

Answers (2)

Yathish Manjunath
Yathish Manjunath

Reputation: 2029

Try the below one :
there is an syntax issue with your query, 
Having should follow after GROUP BY caluse.

Also the columns which you mention in your GROUP BY 
clause should be present in the SELECT list.

SELECT s.id,s.username FROM `instagram_shop` s
INNER JOIN `instagram_shop_picture` p
ON s.id = p.shop_id
WHERE s.deletedAt IS NULL 
GROUP BY s.id,s.username 
HAVING COUNT(s.id) = 0

Upvotes: 0

spencer7593
spencer7593

Reputation: 108380

Move the HAVING clause after the GROUP BY clause.

MySQL is very specific about the order of keywords/clauses in a SELECT statement

Reference: 13.2.9 SELECT Syntax https://dev.mysql.com/doc/refman/5.6/en/select.html

Upvotes: 1

Related Questions