I'm sorry if this question has been asked before. I'm new to SQL and have been trying to search for an answer but to no avail.
I have a dataset which looks something like this:
| Codes | Total |
|---|---|
| 1,2,3 | 4 |
| 5 | 6 |
| 5,7 | 8 |
'Codes' is a varchar column.
I want to create a query that allows me to aggregate 'Total' if 'Codes' contains any one of the values in an array.
Something like this:
SELECT SUM(total)
FROM table
WHERE (codes) = ANY('5','7')
In which case the SUM should be 14.
What should be the appropriate query? Any help would be much appreciated!
You're close. You can use string matching ~ with ANY array comparison, pretty much exactly how you already tried:
demo at db<>fiddle
SELECT SUM(total)
FROM test
WHERE codes ~ ANY(array['5','7'])
| sum |
|---|
| 14 |
If Codes column is supposed to hold arrays of integers, you can reflect that in its definition:
alter table test
alter column codes type int[]
using(string_to_array(codes,',')::int[]);
That makes it lighter and allows you to use adequate array operators, like overlap &&:
SELECT SUM(total)
FROM test
WHERE codes && array[5,7];