CODEWITHSUNDEEP
pythonsqlite
Soap
Soap

Reputation: 303

Retriving the count from one table given a value from another

I'm trying to obtain a value from one table and use that to run count() on another table. I've searched through other questions/answers for help in solving my problem but there isn't one that I've been able to adapt to my scenario in SQLite.

This my code after a unfortunate number of attempts:

c.execute("""SELECT MonkeyHouse.BananaType, count(*)  
FROM MonkeyVillage 
JOIN MonkeyHouse 
   ON MonkeyVillage.BananaType = MonkeyHouse.BananaType 
GROUP BY MonkeyHouse.BananaType""")

So I'm trying to obtain the BananaTypes from the MonkeyHouse and check how many of these BananaTypes are in the MonkeyVillage (and not combined total with the MonkeyHouse).

My code will select the BananaTypes from the MonkeyHouse, but when it returns the count from the MonkeyVillage some counts are accurate while others are not.

How do I solve this?

Update

Values Present in MonkeyHouse (attempting to use the IPs to search the MonkeyVillage)

(u'10.0.0.10') - x1 (records exist)
(u'10.168.77.10') - x2 (records exist)
(u'192.168.77.190') - x1 (records exist)
(u'33.0.0.3') - x1 (records exist)

Values Present in MonkeyVillage (excluding other IPs)

(u'10.0.0.10') - x1 (records exist)
(u'10.168.77.10') - x3 (records exist)
(u'192.168.77.190') - x1 (records exist)
(u'33.0.0.3') - x2 (records exist)

My code returns:

(u'10.0.0.10', 1)  - Successful match
(u'10.168.77.10', 6)  - Unsuccessful 
(u'192.168.77.190', 1) - Successful match
(u'33.0.0.3', 2)  - Unsuccessful

Upvotes: 0

Views: 49

Answers (1)

Scott Cramer
Scott Cramer

Reputation: 304

Based on the data provided, and assuming you are joining on the IP address, the results looks correct for the query. When you join between two tables, your results will be the number of records in table 1 times the number of matching records in table 2, so your numbers are being multiplied.

Assuming the "x2" means 2 records exist for the IP address, and you only want a count of results from the MonkeyVillage table, you may want to try an "IN" clause on the MonkeyVillage table.

c.execute("""SELECT MonkeyVillage.BananaType, count(*)  
FROM MonkeyVillage
WHERE MonkeyVillage.BananaType IN (
SELECT MonkeyHouse.BananaType
FROM MonkeyHouse)
GROUP BY MonkeyVillage.BananaType""")

Upvotes: 2

Related Questions