• Welcome to RadioDJ - Free Radio Automation Software Forum. Please log in or sign up.

A handy SQL query to check song counts in categories

Started by Valdis, November 06, 2015, 05:06:48 PM

Valdis

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:

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

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.
Einstein says: Make things simple, but don't make simple things simpler! (wise) this is just my life lesson you know.

I don't do drugs ( ͡° ͜ʖ ͡°)
The Radiodj tutorials site is now managed by Domstad radio .nl still  Just in English

Valdis

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

I looked over my other snippets and added total and average duration to the query:

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

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

#5
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.
Einstein says: Make things simple, but don't make simple things simpler! (wise) this is just my life lesson you know.

I don't do drugs ( ͡° ͜ʖ ͡°)
The Radiodj tutorials site is now managed by Domstad radio .nl still  Just in English

Valdis

Quote from: Jhonny on November 13, 2015, 05:05:07 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

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

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.

QuoteI'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.
Einstein says: Make things simple, but don't make simple things simpler! (wise) this is just my life lesson you know.

I don't do drugs ( ͡° ͜ʖ ͡°)
The Radiodj tutorials site is now managed by Domstad radio .nl still  Just in English

Vanlen

Forest Green - Music in Nature's Frequency
www.forestgreenradio.ca

Valdis

#10
Quote from: Jhonny 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.
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

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

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 - Rose City FM 89.3 - Warwick, QLD Australia.
Station Engineer - http://rosecityfm.org.au
Community Radio - Bringing you the best of local radio.

Polar

Looks like the domain with the download link for this plugin is down? (axellence.lv)

DJ Garybaldy

Quote from: Polar on April 19, 2017, 12:19:18 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.
Worlds Biggest Fan of RadioDJ

Install MariaDB https://djgarybaldy.blogspot.com/2020/08/installing-maria-db.html

Install RadioDJ: https://djgarybaldy.blogspot.com/2020/08/how-to-install-radiodj-free-radio.html

Into Internet radio for 25 years 1999-2024

RadioDJ 2045 MariaDB 11.2 Windows 11