Changing SUM to count elements of array twice in YugabyteDB YSQL

Question

[Question posted by a user on YugabyteDB Community Slack]

I have an SQL related question.I have a query like this:

SELECT SUM("baseOccupancy")
        FROM "RoomTypes"
        WHERE id IN (134,134)

Now each baseOccupany is 2 and what I want is 4 but since both ids inside IN array are the same, it's just returning 2 instead of 4. Can anyone help me write a query where the query runs for each id inside the array inside the WHERE clause?

Answer 1

Here is a solution:

select sum("baseOccupancy")
from "RoomTypes"
,unnest(array[134,134]) myListOfId
where id = myListOfId

But knowing what is the root problem you want to solve may bring a better solution. Because a where clause is the to filter table rows, not to de-multiply them.

The principal misunderstanding here seems to be what SUM() and COUNT() are doing. COUNT() counts the number of rows, regardless of the value inside the column it's set to count. SUM() reads inside the set column and adds up the values. That makes both principally different. Therefore, if rows are using a unique id, a COUNT() of two id's that exist and have a value for baseOccupancy will result in 2. A SUM() of these will be the total of the two fields.