Simple sql query display?

[rjoseph]

Hi guys

Basically, my database table ("vehicles") contains 5 fields. I have shown an example of what type of data is recorded within these fields below:

MAKE MODEL YEAR PRICE AREA
aston martin 2001 45000 London
aston martin 2002 47000 Manchester
aston martin 2002 50000 Birmingham
porsche 911 1999 32000 Liverpool
porsche 911 2002 38000 Liverpool
porsche 911 2005 43000 Manchester

Basically, I want to create an SQL statement which queries my table and displays the total number of cars there are in certain areas.

Thus, if I want to know how many cars there are in the areas of; London, Manchester, Birmingham, Liverpool, Leeds and Leicester, my page would look something like this:

----------------------
Cars in London: 1
Cars in Manchester: 2
Cars in Birmingham: 1
Cars in Liverpool: 2
Cars in Leeds: 0
Cars in Leicester: 0
----------------------

The "Cars in London: etc etc" text would be hardcoded into my page and so the number "1" would be what the sql query populates.

Has anyone got an ideas how I what my sql statement/query would look like and how I would achieve this?

Any help would be fully appreciated

I look forward to hearing from you

Rod from the UK

[Zoidal]

The solution to your question is:

select area, count(*)
from (database)
group by area
order by area

I found this at:

http://www.itjungle.com/fhg/fhg102605-story01.html

[Zoidal]

However, if you want to take full advantage of using a database, add a column for quantity which would contain 1 or 0 for each entry and then do a sql select with sum. You could then save on coding by something like this:

<cfoutput>Cars In #Area# #Sum#<br /></cfoutput>

Note this is Coldfusion coding but any language (ASP PHP etc) that can access a database has something similar. It will loop thru all Cities and fill in the variable Area and Sum so you don't have to hardcode each line. This could also have been put in a table which would create each <TR> to better format the columns.

<table>
<cfoutput><tr><td>Cars In #Area#</td><td>#Sum#</td></tr></cfoutput>
</table>

This way if you add any new areas to your database, you don't have to change the coding on your web page.

[rjoseph]

Thanks Guys

Your help is fully appreciated!

Best regards

Rod from the UK