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







share|improve this question






















  • For percentage of missing tracks just multiply one of the values by 1.0 because it's float
    – JustMe
    yesterday















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







share|improve this question






















  • For percentage of missing tracks just multiply one of the values by 1.0 because it's float
    – JustMe
    yesterday













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







share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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


















  • 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

















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














 

draft saved


draft discarded


















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






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















 

draft saved


draft discarded



















































 


draft saved


draft discarded














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





















































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







Popular posts from this blog

'app-layout' is not a known element: how to share Component with different Modules

Image
0 My application was working fine until I decided to go with lazy loading: So, my shared component looks like this: import { Component, Renderer2 } from '@angular/core'; export interface FormModel { captcha?: string; } @Component({ selector: 'app-layout', templateUrl: './app-layout.component.html', styleUrls: ['./app-layout.css'] }) export class FullLayoutComponent { } app-layout.component.ts I am using this component in my Another component say school-home.component.html like this: <app-layout> </app-layout> and it was working fine until I created a Module school-home.module.ts like this: import { NgModule } from '@angular/core'; import { CommonModule } from '@angular/common'; import { SchoolhomeRoutingModule ...
Read more

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

Image
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box; } 0 This is a simple project showing cubes on Unity, exported to android project and imported on Android studio. On maifest file it showed following warning: Expecting <uses-feature android:name="android.software.leanback" android:required="false" /> tag I have no information about it. And also on default emptyview android project does not use/mention this feature. I know it is required by TV's and such but why its not added by default? Why is this? And Should I concern about it? android unity3d ...
Read more

WPF add header to Image with URL pettitions [duplicate]

Image
up vote 0 down vote favorite This question already has an answer here: Image source URI requiring authorization 3 answers Add header to all* HTTP requests on a machine 1 answer first of all sorry for my english. Recently I added some security in the backend of my project, and now I need to send a validation code(apiKey) inside the headers to retrieve the images. I have a problem with ALL images in my project that I had attached in my UI. For example, before I had: <Image Height="90" Source="{Binding Video.Thumbnail}"/> Now I need to change my code to create a CUSTOM petition add the apiKey in the header:...
Read more