How to find number of instances in a MySQL table?
Let's say I have a table:
id - number - name
1 - 5555555 - John
2 - 6666666 - John
3 - 5555555 - Jane
Let's suppose I wanted to find the number of times "555555" appears under "number." How could I do that with a MySQL query?
It can also be a workaround with PHP.
- RatchetrLv 71 decade agoFavorite Answer
Whilst: $sqlquery = "SELECT * FROM numbers WHERE number ='5555555';
will work, it's the sort of thing that makes any DBA cringe.
The problem with that is that it selects every column in numbers, for each row where number is 5555555 and returns the whole blasted mess of data to PHP.
If you have 1 million rows with number ='5555555' and 20 columns in each row, you are going to pass back 20 million freakin records, just to count something. Works fine when your DB is small, but it doesn't scale well.
The SQL you want is:
"SELECT count(*) as count FROM numbers WHERE number ='5555555';
That will return a single row with a single column which contains the number you want.
When you can choose between returning one record or 20 million....always chose the 1 record solution.
- Anonymous1 decade ago
SELECT COUNT(number) FROM yourtablename WHERE number = 5555555