A handy SQL query to check song counts in categories

[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:

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

[Jhonny]

V Thanks, I wish I had your skils 

In the run.


 

[attachment deleted by admin]

[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?

[Valdis]

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.

[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.

[Jhonny]

Nice tool 

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.

[Valdis]

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?

[Capt_Fuzzy]

WOW!! Very nice, I can see this becoming very useful Valdis.

Nice job indeed my friend, nice job indeed. 

[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.

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)

[Vanlen]

Nice work indeed.

Thank you

[Valdis]

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.

[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.

[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

Regards,
Chris

[Polar]

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

[DJ Garybaldy]

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.