Problem with GROUP, UNION and COUNT...

[benhaynes]

I am writing a query to create a tree menu, it pulls from a table of music "tracks". In this database there are four "sub_genre" fields for each track, and I need to create a list of all used sub_genres and how many tracks are in each.

The list will look like this:

Acid Jazz (50)
Big Beat (1)
Breakbeat (75)
Chill (27)
Dance (12)
Drum & Bass/Jungle (12)
Hard Rock/Metal (1)
House (2)
...
 

I'm close to having it done, but it is not grouping them properly. Here is my query:

SELECT genre, 1_subgenre as sub, COUNT(title) FROM `tracks` WHERE  `genre` = 'Urban/Hip' GROUP BY `1_subgenre` 
 
UNION 
 
SELECT genre, 2_subgenre as sub, COUNT(title) FROM `tracks` WHERE `genre` = 'Urban/Hip' GROUP BY `2_subgenre` 
 
UNION 
 
SELECT genre, 3_subgenre as sub, COUNT(title) FROM `tracks` WHERE `genre` = 'Urban/Hip' GROUP BY `3_subgenre` 
 
UNION 
 
SELECT genre, 4_subgenre as sub, COUNT(title) FROM `tracks` WHERE `genre` = 'Urban/Hip' GROUP BY `4_subgenre`
 

ANY help would be greatly appreciated!!!

-Ben

[benhaynes]

bump... sorry, this is really important.

[Atli]

Hi.

I'm not getting the whole sub_genre thing.
Could you post the table structure?

I was thinking that you had added a genre column for each track, and then a sub_genre column to narrow it further. But that doesn't really make sense with 4 sub_genre columns.

Unless it is mean to be a tree-view, like, say:

Genre
|->Sub_Genre1
|  |->Sub_Genre2
|  |  |->Sub_Genre3
|  |  |  |->Sub_Genre4
 

In which case your approach is very limited.
That would require a separate table for each genre, where each row is linked to another row.

Am I even close?