Retrieving individual COUNT records

[Ward]

Hi All,

Using MySQL 4.0.25

I am trying to show the count of specific records from one query using the following.

"SELECT area, COUNT(*) FROM table GROUP BY area ORDER BY area".

I can do this in a list using a loop with an example of results

area1(20)
area2(13)
area3(34)

but is it possible to retrieve the count of individual areas using the query above or do I have to write separate queries for each area. The aim is to have each area of a map show their number of records.

[r937]

but is it possible to retrieve the count of individual areas using the query above or do I have to write separate queries for each area. The aim is to have each area of a map show their number of records.

your example shows three areas, with counts for each, and that's great, so why would you want to write separate queries for each area?

[Ward]

It is retrieving the individual areas from the query with their count that I want to do, so that I can make up the map. For instance how would I select just the record for area2(13) without changing the script? Am I making sense?

[r937]

yes, you can do that, just add a WHERE condition

and if you want more than one, use an IN list

SELECT area, COUNT(*)
FROM table
WHERE area in ( 1, 3, 9, 37 )
GROUP BY area
ORDER BY area

[Ward]

And would I call the result by $row['COUNT(3)'] for area 3?

[r937]

no idea (i don't do php)

but i do know you should use a column alias

SELECT area
, COUNT(*) as area_count
FROM table
WHERE area in ( 1, 3, 9, 37 )
GROUP BY area
ORDER BY area

then you will get back a result set of 4 rows with 2 fields each

[Ward]

r937, you've been a great help....again, not the first time you've come to the rescue of my sanity.