Solved

MySQL query question - count(*)

Posted on 2012-03-14
1
399 Views
Last Modified: 2012-03-14
At the moment I'm using this query to count distinct cities with more than 2 businesses.

SELECT DISTINCT bizCity, COUNT(*) as biz_count FROM finance WHERE bizState='$bizState'  GROUP BY bizCity HAVING biz_count > 2

What I would like to do is to select all distinct cities with 100-200 businesses or with 200-300. What is the query in this case?

Thank you
0
Comment
Question by:seopti
1 Comment
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 37722905
DISTINCT is not required as you are already grouping the records.
You can try like this.

SELECT bizCity, COUNT(*) as biz_count FROM finance WHERE bizState='$bizState'  GROUP BY bizCity HAVING COUNT(*) between 100 and 200

SELECT bizCity, COUNT(*) as biz_count FROM finance WHERE bizState='$bizState'  GROUP BY bizCity HAVING COUNT(*) between 200 and 300
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to update the value of duplicated records (except latest one) 2 24
mysql date time 14 38
check mysql insert 12 28
SQL querys that gives me from one table into another. 2 26
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 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