python - How to return the total count of a database entity when one of its rows matches a condition -

i have list of numbers i’m looping through identify if entity in database table matches @ least 1 of these numbers, if so, want count how many times these entities appear in table , return entities along number of time in table. entities may match 1 or more numbers, can duplicate data using pythons set(), unless there’s better way it.

so example

list  = [a, b, c, d]  # unknown number of entries   column 1    column 2   column3 etc  blue            blue            - blue             green           - red             red             b red             c red             c red             - red             - 

so im trying return:

blue, 3 red, 6 

the closest i've been able below, not yielding desired resutls.

for letter in list:      c.execute('select  colour, count(*) table group colour having letter=?', [(letter)]) 

i'm not sure how proceed this, advice appreciated.

in responce @cl.

this kind of data returning. later nest in list contains correct values, first nest contains “random” values. can’t understand how these formed i’m unsure how remove them.

[[(red, 1), (anothercolour, 1)], [(red, 6), (blue, 3), (anothercolour, 5) ]] 

first, entities want count:

select distinct colour mytable letter in ('a', 'b', 'c', 'd') 

then, use filter entities before counting:

select colour,        count(*) mytable colour in (select colour                  mytable                  letter in ('a', 'b', 'c', 'd')) group colour 

when have unknown number of entries, have construct parameter list dynamically:

list = ['a', 'b', 'c', 'd'] params = ','.join('?' _ in list) c.execute("""select colour,                     count(*)              mytable              colour in (select colour                               mytable                               letter in (""" + params + """))              group colour""",           list)