CODEWITHSUNDEEP
mysql
DisplayName
DisplayName

Reputation: 209

Sum only some of the rows of a MySQL Select

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.

Upvotes: 0

Views: 59

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Luuk
Luuk

Reputation: 14899

SELECT 
   CASE WHEN persistent_id IN ('B09423DCE75A0908','1F6E5B2E1FC1CC85','E40063C38F3010B2') 
      THEN 'B09423DCE75A0908' else persistent_id END as persitent_id,
   SUM(play_count)
FROM tracks 
ORDER BY play_count DESC
GROUP BY CASE WHEN persistent_id IN ('B09423DCE75A0908','1F6E5B2E1FC1CC85','E40063C38F3010B2') 
              THEN 'B09423DCE75A0908' else persistent_id END

Upvotes: 0

Related Questions