SQLite – Return Sum of Duplicate Rows Based on Array

arrayselectsqlitesum

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.

Best Answer

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);
Related Question