Author Topic: A handy SQL query to check song counts in categories  (Read 4668 times)

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
A handy SQL query to check song counts in categories
« on: November 06, 2015, 05:06:48 PM »
Hi all,

I've seen that often people here can't tell how many tracks they have in categories or, more often, how many unique artist there are.
So here is an SQL query which has been sitting in my HeidiSQL snippets folder for a while:
Code: [Select]
SELECT id_subcat, subcategory.name, COUNT(id_subcat) songs_in_subcat,
COUNT(DISTINCT songs.artist) AS unique_artists,
COUNT(DISTINCT songs.title) AS unique_titles,
SUM(CASE WHEN songs.enabled <> 1 THEN 1 ELSE 0 END) AS disabled_songs
FROM `songs`
LEFT JOIN `subcategory` ON (songs.id_subcat = subcategory.ID)
WHERE songs.song_type = 0
GROUP BY id_subcat
ORDER BY songs_in_subcat DESC;

It shows category ID, category name, count of songs, count of unique artist, count of unique titles and count of disabled songs.


It is really handy when an error pops up in Windows event logs and all you know is category ID.

Hope this helps,
V
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

Jhonny

  • Hero Member

  • Offline
  • *****
  • 3175
  • Personal Text
    Peace, enjoy life.it's over before you've lived it
    • Jhonny's RadioDJ tutorials.
Re: A handy SQL query to check song counts in categories
« Reply #1 on: November 06, 2015, 05:32:02 PM »
V Thanks, I wish I had your skils  :cool:

In the run.


 

[attachment deleted by admin]
R.T.F.M. means to me, Read The Fantastic Messages.
Make things simple, but don't make simple things simpler!
I don't do drugs, I do tuts. ( ͡° ͜ʖ ͡°) visit https://dj-jhonny.nl

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
Re: A handy SQL query to check song counts in categories
« Reply #2 on: November 06, 2015, 08:15:46 PM »
Hey Jhonny,

I'm glad you find it useful.
I was thinking about writing a simple plugin for RadioDJ which would show these statistics and maybe even include a feature to test how rotation would behave with different repeat rules values. What do you think?
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
Re: A handy SQL query to check song counts in categories
« Reply #3 on: November 06, 2015, 09:25:21 PM »
I looked over my other snippets and added total and average duration to the query:
Code: [Select]
SELECT id_subcat, subcategory.name, COUNT(id_subcat) songs_in_subcat,
COUNT(DISTINCT songs.artist) AS unique_artists,
COUNT(DISTINCT songs.title) AS unique_titles,
SUM(CASE WHEN songs.enabled <> 1 THEN 1 ELSE 0 END) AS disabled_songs,
SEC_TO_TIME(ROUND(SUM(songs.duration))) total_duration,
SEC_TO_TIME(ROUND(AVG(songs.duration))) avg_duration
FROM `songs`
LEFT JOIN `subcategory` ON (songs.id_subcat = subcategory.ID)
WHERE songs.song_type = 0
GROUP BY id_subcat
ORDER BY songs_in_subcat DESC;

It makes easier possible to determine optimal repeat rule values based on the average duration of tracks. I'll try to look how this can be improved even further.
Any suggestions are welcome.
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
Re: A handy SQL query to check song counts in categories
« Reply #4 on: November 13, 2015, 01:42:27 AM »
I've created a plugin to make this thing useful to people who don't speak SQL.
It's rough around edges and doesn't do much right now but I'd be happy if anyone tested it and provided some feedback.

You can grab it here: Plugin_Library_Tools_1.0.zip

Unzip both files to Plugins folder, restart RadioDJ and the plugin will be available by going to OPTIONS > PLUGINS > RadioDJ Library Tools.

This is how it looks:


You should pay attention to cells highlighted in red (light pink actually). There are tooltips with some directions.
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

Jhonny

  • Hero Member

  • Offline
  • *****
  • 3175
  • Personal Text
    Peace, enjoy life.it's over before you've lived it
    • Jhonny's RadioDJ tutorials.
Re: A handy SQL query to check song counts in categories
« Reply #5 on: November 13, 2015, 05:05:07 PM »
Nice tool  :cool:

what is the minimum number of songs, the tooltip says that there are not enough in the subcat.

Will there be a possibility to be able to give the minimum number self?

PS. Of course I'm testing, and give you feedback.
 Also, I'm working on a tut for the plugin. 8)
R.T.F.M. means to me, Read The Fantastic Messages.
Make things simple, but don't make simple things simpler!
I don't do drugs, I do tuts. ( ͡° ͜ʖ ͡°) visit https://dj-jhonny.nl

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
Re: A handy SQL query to check song counts in categories
« Reply #6 on: November 13, 2015, 06:11:43 PM »
what is the minimum number of songs, the tooltip says that there are not enough in the subcat.

Will there be a possibility to be able to give the minimum number self?
Thanks Jhonny,

I've hardcoded the minimum to 10 tracks but my idea was to add advanced logic and show warnings based on average track length and repeat rules. That was not as simple as I thought and the code was left on drawing board.

I have a plan to add cross-referencing rotation rules to show possible problems with rotations and tools to search and fix some common problems with meta data. There are few planned features in my TODO list:
1. Move featured artists from title to artist fields or vice versa.
2. Split track titles for tracks without ID3 tags that haven't been handled by RadioDJ import. When en dash "–" or em dash "—" is used instead of dash (or minus) "-" in file name to separate artist from title, RadioDJ imports track with "Unknown Artist" as artist and file name as title. It could be fixed my Marius, but I can't wait and need to fix many tracks in our library.
3. Search for tracks without correct cue points and re-detect cue points in batch. This could also allow to set start, end, fade-in and fade-out to minimum if needed.

Right now the plugin should be good for identifying problematic categories with too few tracks.
Feel free to ask any questions if anything is unclear. I understand RadioDJ internals really good but that knowledge may affect some decisions I've made when creating the plugin and some things may not be apparent to users.

@Marius & moderators: Could this topic be moved to plugin development board or should I create a new topic on that board?
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

Capt_Fuzzy

  • Hero Member

  • Offline
  • *****
  • 1072
  • Personal Text
    "Quiet numbskulls, I'm broadcasting!" ~ Moe Howard
    • WVRR - Ridgerunners Radio
Re: A handy SQL query to check song counts in categories
« Reply #7 on: November 13, 2015, 06:19:31 PM »
WOW!! Very nice, I can see this becoming very useful Valdis.

Nice job indeed my friend, nice job indeed.  :cool:
Steve 'Capt Fuzzy' Wade
Proud & Satisfied "Long-term" RadioDJ User
(Currently making the transition to v2)

WVRR - Ridgerunners Radio
The best mix of your favorites!

Jhonny

  • Hero Member

  • Offline
  • *****
  • 3175
  • Personal Text
    Peace, enjoy life.it's over before you've lived it
    • Jhonny's RadioDJ tutorials.
Re: A handy SQL query to check song counts in categories
« Reply #8 on: November 13, 2015, 06:44:08 PM »
However Valdis, you're new to the forum, I want to say, I find the plugin very good, hope to see more plugin's of you.

Quote
I've hardcoded the minimum to 10 tracks but my idea was to add advanced logic and show warnings based on average track length and repeat rules. That was not as simple as I thought and the code was left on drawing board.

It is therefore to the number of tracks that you want to put in a subcat.

I personally would like the option how many there should be in a subcategory for a category. (if that is possible)


R.T.F.M. means to me, Read The Fantastic Messages.
Make things simple, but don't make simple things simpler!
I don't do drugs, I do tuts. ( ͡° ͜ʖ ͡°) visit https://dj-jhonny.nl

Vanlen

  • Hero Member

  • Offline
  • *****
  • 1040
    • Voice Talent and production for radio
Re: A handy SQL query to check song counts in categories
« Reply #9 on: November 13, 2015, 08:46:07 PM »
Nice work indeed. :cool:

Thank you
Forest Green - Radio for the New Age.
www.forestgreenradio.ca

Valdis

  • Hero Member

  • Offline
  • *****
  • 562
  • Personal Text
    Radio is a mental condition
    • home
Re: A handy SQL query to check song counts in categories
« Reply #10 on: November 13, 2015, 10:38:40 PM »
However Valdis, you're new to the forum, I want to say, I find the plugin very good, hope to see more plugin's of you.
I may be new to forums but I'm certainly not new to RadioDJ and .NET development or programming in general.

I've listened to feedback and have made few improvements. One is option to enter minimum number of tracks. Then I've added keyboard shortcuts for refreshing the table (F5 or Ctrl+R) and option to view SQL script used by the plugin in Help > The SQL..., so anyone who's interested can see how it works and suggest improvements.

Do not hit Ctrl+F. It is not implemented yet. ;)

I'll try to work out the advanced minimum limit login over weekend.

Grab it here: [Link removed]

Thanks for testing and feedback, guys.
I am, therefore I think.
I design and develop web apps, and have created few RadioDJ plugins - https://axellence.lv/downloads/

JoshTheRadioDude

  • Full Member

  • Offline
  • ***
  • 153
  • Personal Text
    Jack of all trades. Master of most of them.
Re: A handy SQL query to check song counts in categories
« Reply #11 on: January 01, 2016, 06:43:20 PM »
Any possibility we could get playcounts for a specific date range in each category?  I.E. not how many total spins a track has, but the number of times it's been played within the period of time that we query?  I once had scripts to do that in HeidiSQL, but the database schema has changed dramatically since then and I'm not sure I even remember how to program such a thing.
"Jimmy James will hereafter, and for the purposes of this contract only, be referred to as 'God.'"
-- the Act of God clause in Bill McNeal's employment contract with WNYX

chrismadog

  • Full Member

  • Offline
  • ***
  • 215
  • Personal Text
    Be part of the solution, not part of the problem !
    • Rainbow FM
Re: A handy SQL query to check song counts in categories
« Reply #12 on: January 02, 2016, 08:07:55 AM »
Awesome !
I installed it as a plug-in and I can see what I have in what categories and so on - at a glance.

Thank you so much !

+1 :ok:

Regards,
Chris
Presenter - 4SDB - Rainbow FM 89.3 - Warwick, QLD Australia.
Community Radio - Bringing you the best of local radio.
http://rainbowfm.org

Polar

  • New User

  • Offline
  • *
  • 36
  • Personal Text
    Relax
Re: A handy SQL query to check song counts in categories
« Reply #13 on: April 19, 2017, 12:19:18 PM »
Looks like the domain with the download link for this plugin is down? (axellence.lv)

DJ Garybaldy

  • Global Moderator
  • Hero Member

  • Offline
  • *****
  • 3574
  • Personal Text
    I do NOT compile code for RadioDJ I'm just a USER!
    • DJ Garybaldy
Re: A handy SQL query to check song counts in categories
« Reply #14 on: April 19, 2017, 12:25:25 PM »
Looks like the domain with the download link for this plugin is down? (axellence.lv)

The owner of that website doesn't appear to be an active member of this community anymore.

Your only hope is that someone else has the plugin in their repository.
Proud USER of RadioDJ since 2011

https://djgarybaldy.co.uk/maria-db-radiodj/
https://djgarybaldy.co.uk/help-cant-install-radiodj/
https://djgarybaldy.co.uk/radiodj-php-request-script-basic/
https://www.facebook.com/DJGarybaldy/

I cannot support older versions & I'm also not on here on weekends