SQLITE SUBQUERIES <p>I have a SQLITE Table that holds information in the following format. The first column is ID, second is ID_1, third is ID_2 and final column is Volume. </p> <pre><code>ID ID_1 ID_2 Volume 1000 111 0 1000 111 1 1000 111 2 1000 111 3 1000 112 0 1000 112 1 1000 112 2 1000 112 3 1000 113 0 </code></pre> <p>There are 10 or more unique <code>ID</code> values, about 2000 unique <code>ID_1</code> values and 4 unique values for <code>ID_2</code>.</p> <p>At the end I want to select volume for selected IDs, selected ID_1 and selected ID_2. Things work well this far. The output format should be - ID, ID_1, Volume </p> <p>What I am confused about is to get the following data. </p> <p>I need the following data:</p> <p>For a given ID and ID_1, if ID_2 = 1, then</p> <pre><code>Volume = (Volume for ID, ID_1 and ID_2 = 1) - (Volume for ID, ID_1 and ID_2 = 2)- (Volume for ID, ID_1 and ID_2 = 3) </code></pre> <p>Otherwise print volume like it is.</p> <p>I am using python to extract this data in a csv file. I am trying to store ID and ID_1 into value1 and value2 and using that in subquery but it doesnt seem to be working.</p> <p>My code looks like the following:</p> <pre><code>import os import sqlite3 import shutil import csv import time import datetime from pprint import pprint value1 ="va" value2 ="pa" working_dir = "C:\Path" aimsun_db_filename = os.path.join(working_dir,"File.sqlite") conn = sqlite3.connect(filename) c= conn.cursor() SQL = ''' SELECT TableName.ID '''= value1 +''', TableName.ID_1 '''= value2 +''', CASE WHEN ID_2 = 1 THEN (SELECT TableName.volume FROM TableName WHERE ID_2 = 2 and TableName.oid = '''+value1+''' AND TableName.did = '''+value2+''' ) WHEN ID_2 = 2 THEN TableName.volume WHEN ID_2 = 3 THEN TableName.volume ELSE "Other" END, FROM TableName WHERE AND NOT sid = 0 AND NOT ent = 0 ''' with conn: c = conn.cursor() c.execute(SQL) while True: row = c.fetchone() if row ==None: break </code></pre>