CODEWITHSUNDEEP
sqloracle-databaseselectoracle-sqldeveloper
ESD
ESD

Reputation: 545

Complex sql select

I can't figure out how to make this sql select statement...Here are my tables :

enter image description here

I opened the tables concerned by the request

So basically I want to select the number of albums for each interpret. I just can't figure it out... I am currently thinking that I need to do my first select on album like : 

select interpret.no_interpret, count(*)
from album
.
.
.
group by interpret.no_interpret;

and there work from this but I don't know where to go next.

Upvotes: 1

Views: 222

Answers (2)

DRapp
DRapp

Reputation: 48139

I may be missing something, but I'm not seeing the direct relation from your song table to the album...

I would first start by getting the link_interpret_song table joined to the song table and get count of distinct albums. However, I didn't see what appears to be a "No_Album" column in the field list of the song table. I can only guess it IS in there associated to the particular album. I did see media, but to me, that would be like a TYPE of media (digital, download, vinyl, CD) vs the actual ID Key apparent to the album table.

That said, I am thinking there IS such a "No_Album" column in the SONG table.

select
      LIS.No_Interpret,
      COUNT( DISTINCT S.No_Album )
   from
      Link_Interpret_Song LIS
         JOIN Song S
            on LIS.No_Song = S.No_Song
   group by
      LIS.No_Interpret;

Now, that said, if you want the interpret details, take the above results and join that to the interpret table. I've done both distinct album count and total # of songs just as an example of count() vs count(distinct) context... such as

select
      PreCounts.No_Interpret,
      PreCounts.DistinctAlbums,
      PreCounts.ActualSongs,
      I.Name_Interpret,
      I.First_Name,
      I.Stage_Name
   from
      ( select
              LIS.No_Interpret,
              COUNT( DISTINCT S.No_Album ) as DistinctAlbums,
              COUNT(*) as ActualSongs
           from
              Link_Interpret_Song LIS
                 JOIN Song S
                    on LIS.No_Song = S.No_Song
           group by
              LIS.No_Interpret ) as PreCounts
      JOIN Interpret I
         ON PreCounts.No_Interpret = I.No_Interpret

Upvotes: 1

Kirby
Kirby

Reputation: 3709

The question is ambiguous since there isn't a clear indication of how the tables are related. Given assumptions about these relations, your query will likely take on something similar to the following form:

SELECT COUNT(distinct a.no_album) from album a, interpret i, song s 
where i.no_song=s.no_song 
and a.no_album=s.no_album GROUP BY i.no_interpret

Upvotes: 1

Related Questions