I want to sum the duration of multiple selection.
BMPR-00028.mp4 = 7 seconds
SELECT SUM(durata_video_secondi) as durata_video_secondi from catalog WHERE file_video in ('BMPR-00028.mp4','BMPR-00028.mp4')
with this query the result is 7 but I want the SUM of values so I want 14 as result.
I try with
SELECT SUM(durata_video_secondi) as durata_video_secondi from catalog WHERE file_video ='BMPR-00028.mp4' OR file_video ='BMPR-00028.mp4'
but the result is always 7 because the values are the same. But I need the sum of values so I need 14 as result.
Can you help me?
It doesn’t matter how many times you reference the same row, the query will only return one row. SQL is declarative; it doesn’t iterate through the list of
file_video, you’re asking it for all the rows which match the list. There’s only one row that matches no matter how many times you ask for it.
Instead, you need to turn the values into rows and join against them; this ensures there is one row for every value, even duplicates. You can do this by inserting them into a temp table, or for small numbers of values you can join with a subquery and
select sum(durata_video_secondi) from catalog c join ( select 'BMPR-00028.mp4' as file_video union all select 'BMPR-00028.mp4' as file_video ) v on c.file_video = v.file_video;
Answered By – Schwern
Answer Checked By – Terry (BugsFixing Volunteer)