Problems with returning a row in MySQL, ASP

Question

I'm just making the switch from ASP/Access to ASP/MYSQL

I'm working with the default tables in MYSQL.

I'm trying to write a query that counts the records and will allow me to list the contents at the same time.

"SELECT * FROM world.city ORDER BY Name ASC"

I am trying to combine a SELECT COUNT () AS, with a full list.

I can achieve them as seperate queries, but not together. I've tried:

"SELECT Name, SELECT COUNT(*) AS iTotal FROM world.city ORDER BY Name ASC"

But it only returns one row.

Answer 1

Try this:

  SELECT Name, COUNT(*) AS iTotal
    FROM world.city
GROUP BY Name
ORDER BY Name ASC;