Sum only some of the rows of a MySQL Select

Question

I have a select like this:

SELECT persistent_id, artist, name, play_count FROM tracks ORDER BY play_count DESC

Where the result is this:

7FE074BC7C6429F3    artist1 track1  88
EC3CFAF957357763    artist2 track2  87
E40063C38F3010B2    artist3 track6  49
B09423DCE75A0908    artist2 track3  43
1F6E5B2E1FC1CC85    artist2 track4  15
2BB3103CABB9B4DC    artist3 track5  6

How could I rewrite this query so I could specify that the rows so I can specify (using persistent_id) that B09423DCE75A0908 + 1F6E5B2E1FC1CC85, and also E40063C38F3010B2 + 2BB3103CABB9B4DC should be summed? So instead the result would be:

7FE074BC7C6429F3    artist1 track1  88
EC3CFAF957357763    artist2 track2  87
B09423DCE75A0908    artist2 track3  58
E40063C38F3010B2    artist3 track6  55

I tried to do something like:

sum(DISTINCT CASE WHEN persistent_id = 'B09423DCE75A0908' THEN play_count END) AS plays1, 
sum(DISTINCT CASE WHEN persistent_id = '1F6E5B2E1FC1CC85' THEN play_count END) AS plays2

then try to sum plays1 and plays2 as play_count but I didn't get further than putting the play_count of those two persistent_ids on the same row.

question from:https://stackoverflow.com/questions/65944337/sum-only-some-of-the-rows-of-a-mysql-select

Answer 1

seems syou need a case on persistent_id

SELECT case when persistent_id  IN ('B09423DCE75A0908'  , '1F6E5B2E1FC1CC85')  
         THEN 'B09423DCE75A0908'
         when persistent_id  IN ( 'E40063C38F3010B2','2BB3103CABB9B4DC ')   
            THEN 'E40063C38F3010B2'  ELSE persistent_id END persistent_id
            min(artist), min(name), sum(play_count )
FROM tracks 
GROUP BY persistent_id
ORDER BY play_count DESC

could be that for the most older mysql version you must repeat the code for aggregation key in group by

SELECT case when persistent_id  IN ('B09423DCE75A0908'  , '1F6E5B2E1FC1CC85')  
         THEN 'B09423DCE75A0908'
         when persistent_id  IN ( 'E40063C38F3010B2','2BB3103CABB9B4DC ')   
            THEN 'E40063C38F3010B2'  ELSE persistent_id END persistent_id
            min(artist), min(name), sum(play_count )
FROM tracks 
GROUP BY ase when persistent_id  IN ('B09423DCE75A0908'  , '1F6E5B2E1FC1CC85')  THEN 'B09423DCE75A0908'
         when persistent_id  IN ( 'E40063C38F3010B2','2BB3103CABB9B4DC ')   THEN 
            THEN 'E40063C38F3010B2'  ELSE persistent_id END
ORDER BY play_count DESC