Thanks for the screen shots. Now it's more clear what you're doing. As you didn't added any sorting to the query, you didn't get an error. On the other hand, you need to sort the result, to get the desired order in the playlist.
But what kind of queries can I make to create a playlist?
You need to define queries in that way, that the result is one track per row. So, to get a playlist filled for the two cases mentioned in this thread, you could use the following queries:
SELECT 1
FROM MediaLibrary a JOIN
(SELECT artist,avg(play_count) avg_play_count
FROM MediaLibrary
GROUP BY artist) b ON a.artist=b.artist
ORDER BY b.avg_play_count DESC, a.artist,album,discnumber,tracknumber
SELECT 1
FROM MediaLibrary a JOIN
(SELECT artist,count(*) count_tracks
FROM MediaLibrary
GROUP BY artist) b ON a.artist=b.artist
WHERE count_tracks>10
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber
But be aware, that especially for large libraries, queries which are using the MediaLibrary table more than once, are quite slow.
To get it a bit faster, you could use a temporary table for the grouped subqueries. For the second query, you could add the following code to the batch field of your definition.
DROP TABLE IF EXISTS artist_count_tracks;
CREATE TEMPORARY TABLE artist_count_tracks AS
SELECT artist,count(*) count_tracks
FROM MediaLibrary
GROUP BY artist
HAVING count_tracks>10;
CREATE UNIQUE INDEX artist_count_tracks_index ON artist_count_tracks(artist);
The code for the query field, would look like this:
SELECT a.artist,b.count_tracks
FROM MediaLibrary a JOIN artist_count_tracks b ON b.artist=a.artist
ORDER BY b.count_tracks DESC, a.artist,album,discnumber,tracknumber
The predicate for joining both tables needs to be written as "b.artist=a.artist". If you change the order to "a.artist=b.artist", the index artist_count_tracks_index won't be used for some reason, which makes everything even slower.
I thought I could use queries as a "smarter" and more elegant way to make autoplaylists.
Actually, not. Autoplaylist are playlist, which are updating themselves, if the tags in the library change. This won't happen with playlists, which are fed by SQL Tree, because they are simply static playlist. Of course, sending the result again to the playlist, would update the playlist according to the current tags in the media library.
But you can indeed create playlists, which cannot be created with the standard components, which are using title format, like all queries, which build relations between tracks.
Edit: Fixed wrong SQL.