SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieving individual COUNT records

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ward
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And would I call the result by $row['COUNT(3)'] for area 3?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, you've been a great help....again, not the first time you've come to the rescue of my sanity.


Bookmarks

Posting Permissions

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