How to find number of instances in a MySQL table?

Hi!

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?

Update:

It can also be a workaround with PHP.

2 Answers

Relevance
  • 1 decade ago
    Favorite 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.

  • Anonymous
    1 decade ago

    SELECT COUNT(number) FROM yourtablename WHERE number = 5555555

Still have questions? Get your answers by asking now.