Calculation percentage on non-numeric columns

[clemcrock]

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

 

[r937]

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

r937.com | rudy.ca

 

[clemcrock]

Awesome - this is perfect!

Thanks a lot!