Mixing
Percentage of missing tracks and time length
up vote
1
down vote
favorite
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
postgresql
asked 2 days ago
Anon Li
356
add a comment |
up vote
1
down vote
favorite
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
postgresql
asked 2 days ago
Anon Li
356
For percentage of missing tracks just multiply one of the values by 1.0 because it's float
– JustMe
yesterday
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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
postgresql
asked 2 days ago
Anon Li
356
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
postgresql
postgresql
asked 2 days ago
Anon Li
356
asked 2 days ago
Anon Li
356
asked 2 days ago
Anon Li
356
asked 2 days ago
Anon Li
356
asked 2 days ago
Anon Li
356
356
For percentage of missing tracks just multiply one of the values by 1.0 because it's float
– JustMe
yesterday
add a comment |
For percentage of missing tracks just multiply one of the values by 1.0 because it's float
– JustMe
yesterday
For percentage of missing tracks just multiply one of the values by 1.0 because it's float
– JustMe
yesterday
For percentage of missing tracks just multiply one of the values by 1.0 because it's float
– JustMe
yesterday
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372844%2fpercentage-of-missing-tracks-and-time-length%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
For percentage of missing tracks just multiply one of the values by 1.0 because it's float
– JustMe
yesterday