SQLite return sum of duplicate rows based on array

Question

If I have a simple table fruits that looks like this:

+---+---------+------+
| | name | lbs |
+---+---------+------+
| 1 | apple | 0.25 |
| 2 | melon | 3.00 |
+---+---------+------+

In a query, I need to return the sum of the lbs based on an array. For example, I could have the array ('melon', 'melon', 'apple') to return the value 6.25.

A query like this won't work:

SELECT SUM(lbs) FROM fruits WHERE name IN ('melon', 'melon', 'apple');

It returns 3.25 because even though melon is listed twice, it only counts it a single time.

How can I get the select statement to sum duplicate rows as dictated by the given array? I've done a lot of searching of StackExchange, but maybe I'm not using the right keywords to find the answer. Thanks in advance for you help/patience.

Answer 1

Create a (virtual) table with the desired names, then join it with the actual table:

WITH my_array(name) AS (
 VALUES ('melon'), ('melon'), ('apple')
)
SELECT SUM(lbs)
FROM fruits
JOIN my_array USING (name);

• mr.musicman: beautiful! Thank you!