Finding Top Values using SQL Query

user_10649856 0
Viewed 314

Fairly new to SQL and I'm writing a practice query for a music database where I need to pull the top 10 songs by playing hours in January 2009. Here's the schema:

enter image description here

Here's my statement so far. I just wanted to know if I were going in the right direction because I know in order to create the query I need to sum the playing hours of each song and I included that within the ORDER BY clause:

SELECT song_name
FROM music LEFT JOIN client
ON music.id = client.music_id
WHERE client.date BETWEEN '2009-01-01' AND '2009-01-31'
ORDER BY SUM(client.playing_hrs) DESC;
data-analysis select join sql database
4 Answers

In SQL Server you can accomplish your task with this query. See the bottom of my answer for where I explain more of why I am doing certain things in the query so that you can apply it to other RDBMS

select top(10) m.song_name,
       total_song_hrs
       from (
select m.song_name, 
       sum(c.playing_hrs) as total_song_hrs
     from music m 
     
     join clients c 
     on m.id = c.music_id 
     
     where c.date >= '2009-01-01' and c.date <= '2009-01-31'
     group by m.song_name
) s
order by total_song_hrs desc

First you need to join your music table to your clients table in order to access the amount of hours a song has been played. Then you will filter out all songs that have hours played outside of January 2009. Then you will group by a song name, so that you can aggregate the amount of hours the song has been played. Lastly, you will wrap the above in a sub query and in the parent query you will need to use an order by so that you can select the top 10 songs.

user_150258090

Depending on your dialect of SQL, you'll need some kind of LIMIT or TOP clause. Microsoft SQL Server uses TOP. It might also be beneficial to SELECT your total sum as well, using GROUP BY.

SELECT TOP 10 song_name, 
       SUM(client.playing_hrs) as hours_played
FROM music LEFT JOIN client
ON music.id = client.music_id
WHERE client.date BETWEEN '2009-01-01' AND '2009-01-31'
GROUP BY song_name
ORDER BY SUM(client.playing_hrs) DESC;

This'll retrieve the top ten songs and their hours_played.

user_116533570

This is where window functions are best.

Use OVER () and ROW_NUMBER() (the name of ROW_NUMBER function varies across DBMS, it works in sqlite and postgresql)

Assume you have a relation R(a,b)

you can do get the top 10 tuples by ascending value of a using:

WITH T as (
  SELECT a, b, row_number() over (order by a) as n FROM R)
select * from T where n <= 10;

and in your case you can do this:

WITH R as (
   SELECT song_name, sum(client.playing_hrs) as sum
   FROM music LEFT JOIN client
   ON music.id = client.music_id
   WHERE client.date BETWEEN '2009-01-01' AND '2009-01-31'
),
 T as (
      SELECT song_name, row_number() over (order by sum desc) as n FROM R)
    select * from T where n <= 10;

In addition you get the exact position of each tuple (1,2,3,...etc).

Of course you can simplify this query. But this multi-step subquery shows more clearly the solution.

user_23257020

You should have a GROUP BY with the aggregate SUM().

user_163112960
Related