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?