Ytukyg

For each compilation album, output its title, artist, and the following information (in the given order):

•number of tracks, as indicated in the Albums table;

•number of tracks actually present in the Tracks table;

•percentage of missing tracks, formatted as a decimal number with 1 decimal place;

•total running time in seconds (int), calculated as the average length of the album’s tracks listed in the Tracks table, multiplied by the number of tracks reported in the Albums table. Observe that for albums without missing tracks this will be the same as the sum of all track lengths.The output table will have 6 columns, and precisely one row for each compilation album in the Albums table. Compilation albums without tracks in the Tracks table will be included in the output: they will have 100% missing tracks and NULL running time.

Data format:

Artists

- name (unique string)

- type (either person or band)

- country (unique 3 digit code)

Albums

- title (string; may not be unique)

- artist (unique string)

- year (int)

- type (either studio, live, or Compilation)

- rating (int 1 thru 5)

- num of tracks (int)

Tracks

- reference to album (actually two columns: album's title & artist unique string)

- number (int; can be used with album title and artist for identification)

- title (string)

- time length (int, in seconds)

Expected output:

# of tracks according to Albums | # of tracks according to Tracks | % of missing tracks (to 1 decimal place)  | total running time (avg time in Tracks * # of tracks in Albums)

Note: # of each track will not be greater than the total number of tracks of the corresponding album reported in the Albums table

What I have done so far:

SELECT albums.tracks, tracks.NUMBER,

CASE WHEN tracks.NUMBER < albums.tracks THEN (1-tracks.Number/albums.tracks) else 0 end,

(sum(tracks.length)/count(tracks.number)) * albums.tracks

FROM albums join tracks on tracks.artist = albums.artist

WHERE type = 'COMPILATION'

and albums.artist = tracks.artist

group by albums.tracks, tracks.number, albums.title

So, this runs, but the percentage of missing tracks is off because it just returns "1" or "0" in the columns, which shouldn't be the case. The returned time length seems reasonable, but how to include albums missing the tracks on the tracks table and outputting Null?

Thanks