Song History


since last month, there is some mistakes when I want to extract airplay.

For example, in March, the SQL export says that this song has been played 31 times

But in song history, it has been played only one time image

And with the mairlist report module, only one time too

Any idea ?

here is my SQL request :

 SELECT  i.title, i.artist, SUBSTRING(i.duration,1,3), count(*), a9.value, a3.value, a1.value, a2.value, a4.value, a5.value, a6.value, a7.value, a8.value
FROM playlistlog pl 
JOIN items i ON pl.item=i.idx
LEFT JOIN item_attributes a9 ON a9.item=i.idx AND'Album'
LEFT JOIN item_attributes a1 ON a1.item=i.idx AND'ISRC'
LEFT JOIN item_attributes a2 ON a2.item=i.idx AND'Compositeur'
LEFT JOIN item_attributes a3 ON a3.item=i.idx AND'Style'
LEFT JOIN item_attributes a4 ON a4.item=i.idx AND'Leader'
LEFT JOIN item_attributes a5 ON a5.item=i.idx AND'Année'
LEFT JOIN item_attributes a6 ON a6.item=i.idx AND'Pays'
LEFT JOIN item_attributes a7 ON a7.item=i.idx AND'Région'
LEFT JOIN item_attributes a8 ON a8.item=i.idx AND'Langue'
WHERE i.type='Music' AND pl.starttime >= '2021-03-01' AND  pl.starttime < '2021-04-01'  GROUP BY i.title ORDER BY count(*) DESC ;

I think you must specify all non-aggregated columns in the GROUP BY clause.

PostgreSQL even refuses to execute the query when you don’t:

ERROR:  column "i.artist" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  i.title, i.artist, i.duration, count(*), a9.value, a...

It’s exactly that !!!

Thanks Torben !

1 Like