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

Can a sorcerer learn a 5th-level spell early by creating spell slots using the Font of Magic feature?

Image
28 6 $begingroup$ Per the Font of Magic feature, sorcerer can use Flexible Casting to create 5th-level spell slots at level 7, even though they are not typically available until level 9. You can transform unexpended sorcery points into one spell slot as a bonus action on your turn. The Creating Spell Slots table shows the cost of creating a spell slot of [5th level is 7] If such a sorcerer levels up while still having this 5th-level spell slot, can they choose a 5th-level spell as the spell they gain upon levelling up? The Spellcasting feature states: Additionally, when you gain a level in this class, you can choose one of the sorcerer spells you know and replace it with another spell from the sorcerer spell list, which also must be of a level for which you have spell slots.
Read more

ts Property 'filter' does not exist on type '{}'

Image
1 I'm trying to follow Angular Material guide to create a custom filter for autocomplete input text https://material.angular.io/components/autocomplete/overview This is what I have TS import { Component, OnInit } from '@angular/core'; import { TemplateRef } from '@angular/core'; import { FormControl } from "@angular/forms"; import { Observable } from "rxjs"; import { map, startWith, filter } from 'rxjs/operators'; (...) export class AppComponent implements OnInit { title = 'Your Profile'; displayedColumns: string = ['selected', 'name', 'description', 'pnr']; dataSource = ELEMENT_DATA; myControl = new FormControl(); options: any = ['One', 'Two', 'Three']; filteredOptions:
Read more

mat-slide-toggle shouldn't change it's state when I click cancel in confirmation window

Image
1 When I checked the slide toggle it should work as expected. But when I try to uncheck it there is a confirmation window asking if you are sure. When I click cancel in that confirmation window, still the toggle changes to unchecked, which shouldn't happen. It should stay in the same state. Here is my Html and TS Code // html <mat-slide-toggle (change)="change($event)" [checked]="isChecked()" > To-pay </mat-slide-toggle> TS code: // ts change(e) { if(this.checked) { if(confirm("Are you sure")) { console.log("toggle") } else { e.stopPropagation(); console.log("toggle should not change if I click the cancel button") }
Read more