SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculation percentage on non-numeric columns

    Hello,
    trying to calculate the percentages of artist's from each country in our database.

    I am drawing from 2 tables, the artists table which has a short country code and the citylatlong table which has the full country name.

    Since the artists.country column is a varchar(2), is it possible to get the number of artists per country as a percentage.

    Here's the query I'm trying:

    SELECT artists.id, artists.country, countries.countryLONG, (SUM( artists.country ) / COUNT(artists.country )) *100 percentage FROM artists inner join (select countrySHORT, countryLONG from citylatlong GROUP BY countrySHORT, countryLONG) countries ON artists.country = countries.countrySHORT GROUP BY country ORDER BY country ASC limit 10 ;

    The percentage column always comes out as 0.

    Any ideas?

    Thanks,
    Clem c

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT c.country
         , 100.0 * COUNT(*) / 
            ( SELECT COUNT(*) FROM artists ) AS percentage 
      FROM artists 
    INNER 
      JOIN (
           SELECT countrySHORT
                , MAX(countryLONG) AS country 
             FROM citylatlong 
           GROUP 
               BY countrySHORT
           ) AS c
        ON c.countrySHORT = artists.country
    GROUP 
        BY c.country 
    ORDER 
        BY c.country ASC LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    found the solution - here it be:

    SELECT c.country, 100.0 * COUNT(*) / ( SELECT COUNT(*) FROM artists ) AS percentage FROM artists
    INNER JOIN ( SELECT countrySHORT , MAX(countryLONG) AS country FROM citylatlong GROUP BY countrySHORT) AS c ON c.countrySHORT = artists.country
    GROUP BY c.country ORDER BY c.country ASC

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh, my golly goodness, that sure looks a lot like what i posted!!



    p.s. my formatting is way better than yours, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •