Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql mysql count

Posted on 2008-06-11
2
Medium Priority
?
294 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

926 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