How to count number of items in MySQL?

It is better explained in example.

I have the following data in MySQL

   Name   address   age    
   John1   33 St    20    
   John2   22 St    21
   John3   44 St    20
   John4   55 St    21
   John6   44 St 开发者_运维知识库   20
   John5   66 St    35

I would like to get list of rows with counts based on age. But is it possible to get the last row associate with each count. Example

   Name   address   age   Count 
   John6   44 St    20      3
   John4   55 St    21      2
   John5   66 St    35      1

Check out the syntax for GROUP BY

SELECT *, count(*) as Count FROM tableName GROUP BY age;

UPDATE

Let's assume you decide to add an ordering column, like a datetime:

   Name   address   age created   
   John1   33 St    20  2011-04-01 10:00:00
   John2   22 St    21  2011-04-01 09:00:03
   John3   44 St    20  2011-04-01 07:00:20
   John4   55 St    21  2011-04-01 08:45:01
   John6   44 St    20  2011-04-01 13:00:00
   John5   66 St    35  2011-04-01 12:00:40

Then you could accomplish your final goal like so:

  SELECT *, count(*) as Count
    FROM ( SELECT * FROM tableName ORDER BY created DESC ) as newTable
GROUP BY age;

You must use a nested select because otherwise ORDER BY would be applied after GROUP BY.

As Stephen pointed out, you need to do a nested select, but the other way around:

SELECT 
    u.Name,u.Address, u.age, u.NumCount
FROM (SELECT Name, Address, age, count(age) from users group by age) as u
ORDER BY u.NumCount desc