CODEWITHSUNDEEP
pythonpython-2.7sqlite
Soap
Soap

Reputation: 303

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

I have a list of numbers which I’m looping through to identify if an entity in a database table matches at least one of these numbers, if so, I want to count how many times these entities appear in that table and return the entities along with the number of time they a in the table. As the entities may match one or more numbers, I can just the duplicate data by using pythons set(), unless obviously there’s are better way to do it.

So for example 

List  = [a, b, c, d]  # Unknown number of entries


Column 1    Column 2   column3 etc

Blue            a
Blue            -
Blue            a

Green           -
Red             a
Red             b
Red             c
Red             c
Red             -
Red             -

So im trying to return:

Blue, 3
Red, 6

The closest I've been able to get is below, but is not yielding the desired resutls.

for letter in list:

    c.execute('SELECT  colour, COUNT(*) FROM table GROUP BY colour HAVING letter=?', [(letter)])

I'm not sure how to proceed with this, any advice would be very much be appreciated.

In responce to @CL.

This was the kind of data that it was returning. The later nest in the list contains the correct values, but then the first nest contains “random” values. I can’t understand how these are formed so I’m unsure how to remove them.

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

Upvotes: 1

Views: 165

Answers (1)

CL.
CL.

Reputation: 180060

First, get the entities that you want to count:

SELECT DISTINCT colour
FROM MyTable
WHERE letter IN ('a', 'b', 'c', 'd')

Then, use that to filter the entities before counting:

SELECT colour,
       COUNT(*)
FROM MyTable
WHERE colour IN (SELECT colour
                 FROM MyTable
                 WHERE letter IN ('a', 'b', 'c', 'd'))
GROUP BY colour

When you have an unknown number of entries, you have to construct the parameter list dynamically:

list = ['a', 'b', 'c', 'd']
params = ','.join('?' for _ in list)
c.execute("""SELECT colour,
                    COUNT(*)
             FROM MyTable
             WHERE colour IN (SELECT colour
                              FROM MyTable
                              WHERE letter IN (""" + params + """))
             GROUP BY colour""",
          list)

Upvotes: 3

Related Questions