SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Makavely's Avatar
    Join Date
    Jul 2004
    Location
    Romania
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    counting top cities from a table

    Hello everyone,

    I have some problems in counting the top cities from a table like:

    id,job,cities (where cities are string)

    example of rows:

    id| job | cities
    1) 1 | job1 | New York,Los Angeles,California
    2) 2 | job2 | Los Angeles,California
    3) 3 | job3 | Washington DC,Alabama,Alaska
    4) 4 | job4 | Alaska
    ........

    I also have a table with the cities names (id, city) witch i will use to count the number of each city occurrence.


    All i can manage to do is:

    SELECT COUNT(id) as number,cities
    FROM content
    WHERE cities IN (SELECT city FROM cities_names)
    GROUP BY cities ORDER BY number DESC

    As my column cities contain more cities because it's a string, it will count only the rows that have only one city in the "cities" string column. How can i count them correctly?


    Thanks in advance,
    Cristian

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Latvia, Riga
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you cache count of jobs in your cities table (id, city, jobs) ?
    Also I would suggest to use relation table..

  3. #3
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You shouldn't lump together cities into a single string and store it in a single column. Store them in separate rows if you want to issue the query you mentioned. Problem like this is exactly why you should always normalize your tables.

  4. #4
    SitePoint Zealot Makavely's Avatar
    Join Date
    Jul 2004
    Location
    Romania
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i agree with you guys but i want to store data in a single table because it will be faster when you have for example 500.000 results. Inner, Outer joins from my experience have a lot of problems when it comes to large databases.

    So, how can this sql be done without normalization?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Makavely View Post
    i agree with you guys but i want to store data in a single table because it will be faster when you have for example 500.000 results.
    this is a misconception

    Quote Originally Posted by Makavely View Post
    Inner, Outer joins from my experience have a lot of problems when it comes to large databases.
    this is false
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would like to request to read this book if possible:
    http://oreilly.com/catalog/9780596527990

    It contains lots of hacks/tweaks.
    Additionally it also contains the Date Hacks section by our db expert 'Rudy'


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
  •