Help with SQL

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

MeMeMe
Posts: 263
Joined: Fri Dec 23, 2005 11:42 am
Location: In front of my computer

Help with SQL

Post by MeMeMe »

I am modifying DiddleDoo's AutoRateSongs, and need to extract the following information from the database:
1) How many times the current song has been played in a given time period
2) How many total plays of ANY song have been played in the same period, and
3) How many different songs have been played in this period.

For example, if I had played a 15-song playlist three times, I would find that the current song has been played 3 times, and there have been a total of 45 plays, and 15 unique songs have been played during this time.

The following snippet of code from AutoRateSongs tells us how many times a song has been played in a given time:

Code: Select all

StrQuery = "SELECT Count(Played.IdSong) AS arNoP "_ 
+ "FROM Played "_ 
+ "WHERE (((DateDiff(" & D_H & ",Now(),[Played].[Playdate])>=-" & CB_A & ")=True)) "_ 
+ "GROUP BY Played.IdSong "_ 
+ "HAVING Count(Played.IdSong) >0 AND Played.IdSong=" & arSongID 
  
 Dim dbCustomTable 
 Set dbCustomTable = SDB.Database.OpenSQL(StrQuery)

   If Not dbCustomTable.EOF Then 
    dbPlayCount = Int(dbCustomTable.StringByName("arNoP"))
How would I modify it to get the other information (Total Plays, and Number of different songs played).
I would guess I'd have to start with that initial line:
SELECT Count(Played.IdSong)

Changing it to whatever code represents Count(Played.All) and Count(Played.EachDifferentSongID) would seem to do the trick - what would be the commands for this?
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy »

in a given time period
As far as I know, neither of your 3 questions are possible (if I understood them well).

Available stored information for a song:
- Times it has been played (play count)
- Date&Time when it was last played (last played)

So getting a song that has been played X times in a certain period is not possible.
It is possible however, to find out that a song has been played in a certain period (last played), and get the total playcount for the song play count).

Cheers
Steegy

PS: If you are modifying the script, can you pass all modifications through the original creator (i.e. DiddeLeeDoo)?
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
MeMeMe
Posts: 263
Joined: Fri Dec 23, 2005 11:42 am
Location: In front of my computer

Post by MeMeMe »

Well, the first one is being done already, in the AutoRateSongs script.

That SQL snippet I included in the first post takes the current song, and tells you how many times it's been played in a time period defined by the D_H and CB_A variables (hours or days, and how many of them).

The MM database has a Played table - this has a list of every song played, every time it was played. (There's a time field, and a SongID field which shows you which song was played at that time when you match up with the songs table).
So it is possible, definitely.
I just don't know SQL, so I don't know how to do it.
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy »

As far as I know
Well... it seems to be not far enough. :-?
From a recent thread, I got the idea that the Played table wasn't necessary. But I remembered bad. The actual story is that the Played table keeps information that isn't used within MM. A script can use it though, when it has to
What I said was only about the information in the Songs table..
So it *is* probably possible. Thanks for letting me learn today :) .
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Post by Pablo »

It *is* definitely possible. I'm in a rush now and I'll be away for a few days, but if I get a few minutes I'll post the code :wink:
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Here is the SQL part:

Code: Select all

SELECT Count(PlayStat.CountOfIdPlay) AS UniqueSongs, Sum(PlayStat.CountOfIdPlay) AS TotalPlay
FROM
(SELECT Count(Played.IdPlay) AS CountOfIdPlay
FROM Played
WHERE (((Played.PlayDate) Between #4/1/2006# And #4/20/2006#))
GROUP BY Played.IdSong) As PlayStat
Time period is between 1 April to 20 April 2006. You can change it to whatever you want. You can also include time if you want.
Actually this date/time thing could be tricky. Let me know if you need help with it.

The SQL gives you two columns, one with unique songs and the other with Total Plays, in the given period.

Let me know if you need two different SQL's.

I dont know VBScript so I can't help you with that...


/Bex
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
DiddeLeeDoo
Posts: 1017
Joined: Wed Mar 01, 2006 1:09 am
Location: In a jungle down under
Contact:

Post by DiddeLeeDoo »

I'll work a bit with that Catch-Up on Ratings script today, and I will try to add a way for the user to do reasonable calibration before executing the script.

Have the working SQL at the moment, but struggle a bit with ODBC errors while doing it by scripting, so I'll try to solve that before posting.

Added: BTW, Bex, that's a cool SQL.

Added: Had success with the scripting SQL finally... just have to make it a bit fancy, and with a bit of luck I'll try to get it posted in a few hours..
Image
DiddeLeeDoo
Posts: 1017
Joined: Wed Mar 01, 2006 1:09 am
Location: In a jungle down under
Contact:

Post by DiddeLeeDoo »

Ok, at this stage, only an estimator... estimate results and give a report to screen as a message box.

Maybe something to play with, till I get it finished, with 'Catch-Up' function, a Weekly function, and a Dialog Box to put in numbers to try with. At this stage you have to place numbers in the scripts Parameter section, save, and then run it from MM.

http://www.mediamonkey.com/forum/viewto ... &start=174

My results
Image
Image
MeMeMe
Posts: 263
Joined: Fri Dec 23, 2005 11:42 am
Location: In front of my computer

Post by MeMeMe »

Bex wrote:Here is the SQL part:
Time period is between 1 April to 20 April 2006. You can change it to whatever you want. You can also include time if you want.
Actually this date/time thing could be tricky. Let me know if you need help with it.
I'd want to use the difference between "Now()" and a given number of hours or days ago. The code snippet in my first post has the time code I was planning to use. ("D_H" is a variable equalling "h" or "d" and sets hours or days, while "CB_A" is a number, like 30 - which would mean either 30 hours or 30 days, depending on D_H).
So, if you could modify your snippet to take that into account, that would be fantastic.
The SQL gives you two columns, one with unique songs and the other with Total Plays, in the given period.

Let me know if you need two different SQL's.
Thanks, Bex. I didn't realise you'd be able to get both of those factors in the same table. Cool. I had been working on the assumption that I'd be using two separate SQLs, but this is more elegant.
Just to be clear I understand this:
This table will return one column containing all the different songs that have been played in the time period, and the second column will show how many times each of those songs have been played?

If so, that opens up new possibilities. Would you be able to create a third column to this table, and include the song's duration in that third column?
I dont know VBScript so I can't help you with that...
I know some visual basic; if (when!) I get stuck, I'm sure there are others that can help. But this SQL is brilliant - thanks again.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Ok here we go:
This SQL gives one row with three columns.
CountOfSong = Count of times the specified song has been played in the period.
CountUniqueSongs = Number of different songs (incl the specified) played in the period.
TotalPlays = Sum of all times any song has been played in the period.

SongID = 685
Period = Now - 30 days

Code: Select all

SELECT Sum(Inline.song) AS CountOfSong, Count(Inline.CountOfIdPlay) AS CountUniqueSongs, Sum(Inline.CountOfIdPlay) AS TotalPlays
FROM
(SELECT Sum(IIf([idsong]=685,1,0)) AS Song, Count(Played.IdPlay) AS CountOfIdPlay
FROM Played
WHERE (Played.PlayDate)>=DateAdd("d",-30,Now())
GROUP BY Played.IdSong) AS Inline
If D_H is 'd' or 'h' then I believe the VB syntax would be something like this:

Code: Select all

StrQuery = "SELECT Sum(Inline.song) AS CountOfSong, Count(Inline.CountOfIdPlay) AS CountUniqueSongs, Sum(Inline.CountOfIdPlay) AS TotalPlays"_
+ "FROM (SELECT Sum(IIf([idsong]="& arSongID &",1,0)) AS Song, Count(Played.IdPlay) AS CountOfIdPlay"_
+ "FROM Played"_
+ "WHERE (Played.PlayDate)>=DateAdd(" & D_H & ",-" & CB_A & ",Now())"_
+ "GROUP BY Played.IdSong) as Inline"
But If D_H is d or h then I believe the VB syntax would be like this:

Code: Select all

StrQuery = "SELECT Sum(Inline.song) AS CountOfSong, Count(Inline.CountOfIdPlay) AS CountUniqueSongs, Sum(Inline.CountOfIdPlay) AS TotalPlays"_
+ "FROM (SELECT Sum(IIf([idsong]="& arSongID &",1,0)) AS Song, Count(Played.IdPlay) AS CountOfIdPlay"_
+ "FROM Played"_
+ "WHERE (Played.PlayDate)>=DateAdd('" & D_H & "',-" & CB_A & ",Now())"_
+ "GROUP BY Played.IdSong) as Inline"
In both codes songID = arSongID.

There's a big chance that my VB syntax is wrong...


/Bex
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
MeMeMe
Posts: 263
Joined: Fri Dec 23, 2005 11:42 am
Location: In front of my computer

Post by MeMeMe »

Wow, thanks. Very nice.
It's very helpful that you included the version with explicit non-variable values as well as one with variables. That one's much easier to read.

"TotalPlays = Sum of all times any song has been played in the period. "
Do this mean the the total time the song on this row has been played in this period?
That's what I think it means, but it never hurts to check!
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

The SQL only gives one row, always!

Ex.
Let's say that you in a period you have played:
- SongId 789, two times (This is 'arSongID', the specified song)
- SongId 114, five times
- SongId 376, seven times

In this case the result of the SQL would be:
CountOfSong -> 2
CountUniqueSongs -> 3
TotalPlays -> 14

This is what you wanted, isn't?

/Bex
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
MeMeMe
Posts: 263
Joined: Fri Dec 23, 2005 11:42 am
Location: In front of my computer

Post by MeMeMe »

Doh! That is what I wanted yes, but I thought it was producing a table of rows and columns, and then in the VBScript afterwards I was selecting the row I wanted to draw data from.
Sorry for not understanding. Thanks for bearing with me, and <puts on best puppy dog expression>here's what I would actually like:

Count of Song = count of times the specific song has been played in the period
CountUniqueSongs = Number of different songs (incl the specified) played in the period.
Total Plays = Number of total songs played, including duplicates (not duration, but discrete plays)

I'd also like some duration columns (if it's not too much trouble - I can do without these, but they would be nice to have):
SongTime = either the length of the song (for one play), or the total time this song has been played in the period (either will do, as long as I know which it is).

and either:
TotalPlayTime = the duration of all songs played in the period (this is I think what you had as Total Plays)
or
AverageSongTime = the totalPlayTime divided by the count of unique songs (but with TotalPlayTime, I can calculate this with VBScript, so it's optional).
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

MeMeMe wrote: Count of Song = count of times the specific song has been played in the period
CountUniqueSongs = Number of different songs (incl the specified) played in the period.
Total Plays = Number of total songs played, including duplicates (not duration, but discrete plays)
That's what you got or do i missunderstand something here?

Anyway here's a modified SQL with the requested extra fields. I also made the code a little bit smarter:

Code: Select all

SELECT Sum(IIf([idsong]=10784,[CountOfIdPlay],0)) AS SongPlayed, Count(Inline.CountOfIdPlay) AS CountUniqueSongs, Sum(Inline.CountOfIdPlay) AS TotalPlays, Sum(IIf([idsong]=10784,[SongLength],0)) AS SongTime, Sum(Inline.SongLength) AS TotalPlayTime
FROM
(SELECT Count(Played.IdPlay) AS CountOfIdPlay, Played.IdSong, Songs.SongLength
FROM Songs, Played
WHERE Songs.ID = Played.IdSong AND Songs.SongLength>0 AND Played.PlayDate>=DateAdd("d",-30,Now())
GROUP BY Played.IdSong, Songs.SongLength) AS Inline
I've changed the name of CountOfSong to SongPlayed.
SongTime = Time of specified song.
TotalPlayTime = Total time of all songs played in period.

I didnt format the new time fields since i figured it's better to do that after all various calculations are made?
Formatcode:

Code: Select all

Format([SongLength]/1000/86400;"nn:ss")
Or if even nicer if theres's a change that SongLength > 60 min

Code: Select all

IIf([SongLength]>3600000,Format([SongLength]/1000/86400,"hh:nn:ss"),Format([SongLength]/1000/86400,"nn:ss"))
Enjoy!
/Bex
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
DiddeLeeDoo
Posts: 1017
Joined: Wed Mar 01, 2006 1:09 am
Location: In a jungle down under
Contact:

Post by DiddeLeeDoo »

You certainly are on top of SQL Bex!! those are interesting reads, and really nice to have available here in the forum!

All is good help to get the very best music 'machine' even better! :)
Image
Post Reply