Trying to count multiple values and total

Question

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?

Answer 1

Try this for a count of competitions in each genre:

SELECT 
  COUNT(competition.competition_id) as competition_count, 
  genre.genre_id,
  genre.name
FROM 
  genre
  INNER JOIN competition on ( genre.genre_id = competition.genre_id )
  INNER JOIN band on ( band.band_id = competition_band.band_id )
GROUP BY genre.genre_id, genre.name
ORDER BY genre.name;

A count of bands who have entered competitions sounds like a different query to me:

SELECT 
  COUNT(band.band_id) as bands_in_competitions_count, --double counts bands in multiple competitions
  COUNT(distinct band.band_id) as bands_in_competitions_in_this_genre,
  genre.genre_id,
  genre.name
FROM 
  genre
  INNER JOIN competition on ( genre.genre_id = competition.genre_id )
  INNER JOIN competition_band on ( competition.competition_id = competition_band.competition_id )
  INNER JOIN band on ( band.band_id = competition_band.band_id )
GROUP BY genre.genre_id, genre.name
ORDER BY genre.name;