Complex SQL query based on adding the values of several columns

Question

I have a table called music_lib which has a bunch of columns and over 2000 entries.

id | track_id | total_plays | total_downloads | likes | active | etc | etc

I'm trying to select only the most recent 50 active entries, based on id but I want to order them relative to the combined values (all ints) of the following columns :

total_plays + total_downloads + likes

So basically I want the latest 50 entries ordered by how popular they are (popularity being based on the combined plays, downloads and likes of each entry).

But I have no idea how to formulate this query with PHP / MySQL (using wordpress)..

I know I can get the most recent 50 entries with the following but no idea how to order those results based on the combined figure of the three columns as mentioned above.

$getpodcasts = $wpdb->get_results( "SELECT * FROM music_lib WHERE active = 1 ORDER BY id DESC LIMIT 50");

Sorry track_id should have been 'likes', my bad, will correct the question — Grant
– Grant, Commented Jan 7, 2017 at 20:01

Veshraj Joshi · Accepted Answer · 2017-01-04 19:14:46Z

1

then use order by and your calculation and then id like below-

$getpodcasts = $wpdb->get_results( "
    SELECT * FROM music_lib 
    WHERE active = 1 
    ORDER BY (track_id + total_plays + total_downloads) DESC,
             id desc 
    LIMIT 50");

first it looks for track_id + total_plays + total_downloads and sort them accordingly and then sort them by id

edited Jan 4, 2017 at 19:14

answered Jan 4, 2017 at 18:45

Veshraj Joshi

3,5874 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Veshraj Joshi Over a year ago

let me know whether it works for you or not... if helpful please accept as answer and upvote to

Grant Over a year ago

Thanks for your reply, this partially works. It shows only 50 in the correct order, but it is calculating the sum of the 3 columns from EVERY entry in the table, not JUST the last 50 which I am trying to do. Any ideas?

Veshraj Joshi Over a year ago

you can remove - limit 50

Veshraj Joshi Over a year ago

actually i could not understand your need ... thanks for your reply

Collectives™ on Stack Overflow

Complex SQL query based on adding the values of several columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related