dcsimg
www.webdeveloper.com
Results 1 to 4 of 4

Thread: Simple sql query display?

  1. #1
    Join Date
    Sep 2007
    Posts
    60

    Simple sql query display?

    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

  2. #2
    Join Date
    Sep 2007
    Posts
    12
    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

  3. #3
    Join Date
    Sep 2007
    Posts
    12

    Wink

    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.
    Last edited by Zoidal; 11-07-2007 at 08:26 AM.

  4. #4
    Join Date
    Sep 2007
    Posts
    60
    Thanks Guys

    Your help is fully appreciated!

    Best regards

    Rod from the UK

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles