musicbrainz unable to count artists by country

Question

Hi i am using the musicbrainz database and i am having trouble counting all the Artists for each country, but no matter what country's i try i get the same error, even when i try using like. PLease can anyone show me what i have done wrong ?

ERROR: column "%u%" does not exist LINE 7: WHERE AREA.NAME LIKE "%u%"

SELECT COUNT(artist.name)
FROM artist
JOIN area ON artist.area = area.id 
JOIN label ON area.id = label.area
JOIN country_area ON area.id = country_area.area
JOIN release_country ON country_area.area = release_country.country
WHERE AREA.NAME LIKE "%dom"
GROUP BY release_country.country
limit 5;

UPDATE:

musicbrainz_db=> SELECT COUNT(artist.name)
musicbrainz_db-> FROM artist
musicbrainz_db-> JOIN area ON artist.area = area.id
musicbrainz_db-> JOIN label ON area.id = label.area
musicbrainz_db-> JOIN country_area ON area.id = country_area.area
musicbrainz_db-> JOIN release_country ON country_area.area = 
release_country.country
musicbrainz_db-> WHERE AREA.NAME LIKE '%dom'
musicbrainz_db-> GROUP BY release_country.country
musicbrainz_db-> limit 5;

ERROR: canceling statement due to statement timeout

My teacher just came over and said it wont work with out a sub query ?

select area.name, label_count
from area
where label_count in 
(
    select area.name, count(label.id) as "label_count"
    from area
    JOIN label on area.id = label.area
    group by area.name
);

the sub query works fine but the main query fails ? any idea why.

show more code .. plase . show the code where you use this query — ScaisEdge, Nov 20 '17 at 13:55
Try with single quotes instead of double. Also, you usually (almost always?) want to use a SELECT TOP with a LIMIT statement. — Jacob H, Nov 20 '17 at 13:56
im running it in a terminal to a internal server, that is all the code. — edwin, Nov 20 '17 at 14:06
your teacher is right. Your third join is not between area and another table so it will not be executed. You need a subquery for the third join and the primary query for the first two — Lelio Faieta, Nov 20 '17 at 14:22

score 1 · Answer 1 · answered Nov 20 '17 at 13:59

SELECT COUNT(artist.name)
FROM artist
JOIN area ON artist.area = area.id 
JOIN label ON area.id = label.area
JOIN country_area ON area.id = country_area.area
JOIN release_country ON country_area.area = release_country.country
WHERE AREA.NAME LIKE '%u%'
GROUP BY release_country.country
limit 5;

score 1 · Answer 2 · answered Nov 20 '17 at 13:59

See MySQL official documentation

https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

and try with single quote

SELECT COUNT(artist.name)
FROM artist
JOIN area ON artist.area = area.id 
JOIN label ON area.id = label.area
JOIN country_area ON area.id = country_area.area
JOIN release_country ON country_area.area = release_country.country
WHERE AREA.NAME LIKE '%dom'
GROUP BY release_country.country
limit 5;

double quotes are used for column name

musicbrainz unable to count artists by country

2 Answers2