SELECT
COUNT (DISTINCT competition.genre_id) genrecount,
COUNT(DISCTINCT band.band_id),
genre.name,
genre.genre_id
FROM genre, competition_band, competition, band
WHERE
genre.genre_id = competition.genre_id
AND band.band_id = competition_band.band_id
AND competition.competition_id = competition_band.competition_id
GROUP BY genre.genre_id, band.band_id, genre.name, competition.competition_id
ORDER BY name;
Basically I am trying to show how many competitions of each genre are being held and how many bands have entered competitions of each genre.
Really stumped, the results of this just show genrecount and band_id as a series of 1's and multiple genres.
can anyone help?
Band (band_id, band_name uni_id, band_leader)
COMPETITION (COMPETITION_ID, COMPETITION_TITLE, COMPETITION_DATE, START_TIME, SPONSOR_ID, GENRE_ID)
COMPETITION_BAND (BAND_ID, COMPETITION_ID, SET_TIME)
GENRE(GENRE_ID, NAME)
Do we need a sub query?