[SOLVED] SELECT SUM WHERE multiple values even if are equals

Issue

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?

Thanks

Solution

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 union all.

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;

Demonstration.

Answered By – Schwern

Answer Checked By – Terry (BugsFixing Volunteer)