Select multiple value

Question

I have a string of values like '409.7,409,334.2' ; the number of elements varies. I like to COUNT how many records there are with 409.7 + how many records there are with 409 + how many records there are with 334.2 Something like?

SELECT COUNT "PrimaryKey" 
FROM Data 
WHERE "Program Number" IN '409.7,409,334.2' AND "Date" >=  1/1/2020

Where is the string coming from? Couldn't you split it up before creating the SQL? — Barmar, Sep 24 '20 at 23:07
Do you need separate counts for each program number, or just the total of all of them? — Barmar, Sep 24 '20 at 23:09
```WHERE FIND_IN_SET(`Program Number`, '409.7,409,334.2')``` — Barmar, Sep 24 '20 at 23:10
If you can reformat it as a list, then it should be ```Where `Program Number` IN (409.7, 409, 334.2)``` — Barmar, Sep 24 '20 at 23:11

GMB · Answer 1 · 2020-09-24T23:21:33.740

0

If you just want the overall count, you can use find_in_set() and aggregation:

select count(*) cnt
from data
where find_in_set(program_number, '409.7,409,334.2')

If you want a per-element count, then one option is conditional aggregation:

select 
    sum(find_in_set(program_number, '409.7,409,334.2') = 1) cnt1,
    sum(find_in_set(program_number, '409.7,409,334.2') = 2) cnt2,
    sum(find_in_set(program_number, '409.7,409,334.2') = 3) cnt3
from data
where find_in_set(program_number, '409.7,409,334.2')

If you have a lot of elements the the csv list, you might as well generate rows than column. This gives you the count of matches for each element index:

select find_in_set(program_number, '409.7,409,334.2') element_index, count(*) cnt
from data
group by element_index

edited Sep 24 '20 at 23:21

answered Sep 24 '20 at 23:07

GMB

216,147
25
84
135

I don't think `program_number` is a comma-separated list, just this literal string. – Barmar Sep 24 '20 at 23:08
@Barmar: yes I am assuming that it's a csv string, as showned by OP: `'409.7,409,334.2'` – GMB Sep 24 '20 at 23:10
I don't know how many elements there will be in the list. Probably around a dozen – user2548919 Sep 24 '20 at 23:11
You seem to have it backwards. `Program Number` is a single number, he wants to check if it's in a comma-separated list. – Barmar Sep 24 '20 at 23:12
@Barmar: ah yes, I see your point, thank you. I changed my answer. – GMB Sep 24 '20 at 23:16

Select multiple value

1 Answers1