?
Solved

sql mysql count

Posted on 2008-06-11
2
Medium Priority
?
296 Views
Last Modified: 2010-03-19
SELECT COUNT(*) AS `Rows`, `Ssurname` FROM `member` GROUP BY `Ssurname` Where Rows > 2
ORDER BY `Rows` Desc


 MySQL said: Documentation
#1064 - 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 'Where Rows > 2
ORDER BY `Rows` Desc
LIMIT 0, 30' at line 1


How can icorrect the code above
0
Comment
Question by:teera
2 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 1000 total points
ID: 21757859
Conditions for the grouping are named having like:

 SELECT COUNT(*) AS `Rows`, `Ssurname` FROM `member` GROUP BY `Ssurname` HAVING COUNT(*) > 2
0
 
LVL 6

Assisted Solution

by:dragos_craciun
dragos_craciun earned 1000 total points
ID: 21757865
You must write

SELECT COUNT(*) AS `Rows`, `Ssurname` FROM `member` GROUP BY `Ssurname`
HAVING COUNT(*) > 2
ORDER BY `Rows` Desc

WHERE can be used only for individual rows
For filtering on aggregates, use HAVING
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question