How sum certain duplicated data?-CodePudding

I have a list of ids, some ids can be duplicated. How to sum values for the same id in one query?

SELECT SUM(`power`)
FROM `stats`
WHERE `template` IN (489191, 489191, 489100)

The template 489191 has power = 1 and 489100 has power = 0 so I need to get 2 as the result.

CodePudding user response：

You need to join with table value data to get the desired number of rows e.g.:

select sum(stats.power)
from (
    select 489191 as template union all
    select 489191 union all
    select 489100
) as vlist
join stats on vlist.template = stats.template

If you're using MySQL 8 or later you can use the values table value constructor instead of union all:

select sum(stats.power)
from (values
    row(489191),
    row(489191)
    row(489100)
) as vlist(template)
join stats on vlist.template = stats.template