0

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.

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);
  • beautiful! Thank you! – mr.musicman May 9 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.