CODEWITHSUNDEEP
pythonsqlsqlite
nasia jaffri
nasia jaffri

Reputation: 823

Arithmetic Operation in a SQL query (nested Select statement) using Python

I am trying to do an arithmetic operation in a SQL query using Python (I am using sqlite3). My SQL Table (TwTbl) has a coloumn geo_count(number). I have to count the number of entries in which the Geo_count Coloumn has a number greater than 0, and also count the number of entries with Geo_count = 0 and then subtract them. i.e.

(number of entries with Geo_count = 0) - (number of entries with Geo_count > 0)

I have to write a nested select statement for that. 

import sqlite3
c.execute("SELECT (COUNT(SELECT geo_count FROM TwTbl WHERE geo_id == 0) – 
          COUNT(SELECT geo_count FROM TwTbl WHERE geo_count IS <> 0)) FROM TwTbl").fetchall()

This is giving me a syntax error

Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "SELECT": syntax error

I tried writing this query in another way. It is not giving me any syntax error, but not the expected results. If I run the following query I get 0.

c.execute("select count(geo_count) from TwTbl where geo_count == 0 - 
         (select count(geo_count) from TwTbl where geo_count <> 0)").fetchall()

Although If I run the queries individually the results are as follows:

c.execute("select count(geo_count) from TwTbl where geo_count <> 0").fetchall()
>>> 13
c.execute("select count(geo_count) from TwTbl where geo_count ==0").fetchall()
>>> 880

I am not sure about the correct syntax. Not sure what am I doing wrong.

Upvotes: 1

Views: 2005

Answers (1)

Imre L
Imre L

Reputation: 6249

SELECT SUM(geo_count==0) - SUM(geo_count<>0) FROM TwTbl

Upvotes: 1

Related Questions