En días pasados me tocoó hacer un COUNT() a una consulta, esta me generaba sólo una línea de resultado porque esta función agrupa al momento de hacer la consulta.
La solución fue simple, agregas un GROUP BY a la consulta tantos campos quieras dividir o datos a recopilar por separado.
Un ejemplo:
+--------+----------------------+-----------+----------------+ | aut_id | aut_name | country | home_city | +--------+----------------------+-----------+----------------+ | AUT001 | William Norton | UK | Cambridge | | AUT002 | William Maugham | Canada | Toronto | | AUT003 | William Anthony | UK | Leeds | | AUT004 | S.B.Swaminathan | India | Bangalore | | AUT005 | Thomas Morgan | Germany | Arnsberg | | AUT006 | Thomas Merton | USA | New York | | AUT007 | Piers Gibson | UK | London | | AUT008 | Nikolai Dewey | USA | Atlanta | | AUT009 | Marquis de Ellis | Brazil | Rio De Janerio | | AUT010 | Joseph Milton | USA | Houston | | AUT011 | John Betjeman Hunter | Australia | Sydney | | AUT012 | Evan Hayek | Canada | Vancouver | | AUT013 | E. Howard | Australia | Adelaide | | AUT014 | C. J. Wilde | UK | London | | AUT015 | Butler Andre | USA | Florida | +--------+----------------------+-----------+----------------+SELECT country,COUNT(*) FROM author GROUP BY country;
mysql> SELECT country, COUNT(*) -> FROM author -> GROUP BY country; +-----------+----------+ | country | COUNT(*) | +-----------+----------+ | Australia | 2 | | Brazil | 1 | | Canada | 2 | | Germany | 1 | | India | 1 | | UK | 4 | | USA | 4 | +-----------+----------+ 7 rows in set (0.00 sec)