Group and Sum on Multiple Joins

Date: March 13th 2016
Last updated: March 13th 2016

This entry follows on from sum group on query join. However, in this entry I merge contents of three tables. The example uses three tables; animals, patients and vet_visit. All tables were created previously (see foreign keys) but some additional data are added here.

import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# add data to vet_visit table
# patient 3 = "peanut", animaltype 1 = "dog" 
visits = [(3, 1, 35.00, ), \
          (3, 1, 385.00, ), \
          (3, 1, 13.00, ), \
          (1, 1, 35.00, ), \
          (3, 1, 73.0, ), \
          ]

# execute
cur.executemany('INSERT INTO vet_visit (visitpatientid, visitanimalid, \
#                visitprice) VALUES (?, ?, ?);', visits)

# execute (Get the sum cost for each patient of type dog)
cur.execute('SELECT AVG(vet_visit.visitprice), patient.patientname, animals.animaltype \
             FROM \
                 vet_visit \
                 INNER JOIN animals \
             ON \
                 animals.animalid == vet_visit.visitanimalid \
                 \
                 INNER JOIN patient \
             ON \
                 patient.patientid == vet_visit.visitpatientid \
             WHERE \
                 animals.animaltype="dog"\
             GROUP BY \
                 patient.patientname;')

rows = cur.fetchall()
for row in rows:
    print(row)

# commit and close
con.commit()
con.close()

Output (only two dogs so far)

# average bill for two patients
# (126.5, 'peanut', 'dog')
# (57.75, 'rover', 'dog')

Add data for two more dogs (patients)

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# add data to vet_visit table
# patient 4 = "peanut", animaltype 1 = "dog" 
visits = [(4, 1, 10.0), \
          (5, 1, 15.0), \
          (4, 1, 24.0), \
          (5, 1, 23.0),
          (4, 1, 10.0), \
          (5, 1, 15.0), \
          (4, 1, 24.0), \
          (5, 1, 23.0),
          ]

cur.executemany('INSERT INTO vet_visit (visitpatientid, visitanimalid, \
                 visitprice) VALUES (?, ?, ?);', visits)

# commit and close
con.commit()
con.close()

Rerun query

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# execute (Get the AVERAGE bill for each dog)
cur.execute('SELECT AVG(vet_visit.visitprice), patient.patientname, animals.animaltype \
             FROM \
                 vet_visit \
                 INNER JOIN animals \
             ON \
                 animals.animalid == vet_visit.visitanimalid \
                 \
                 INNER JOIN patient \
             ON \
                 patient.patientid == vet_visit.visitpatientid \
             WHERE \
                 animals.animaltype="dog"\
             GROUP BY \
                 patient.patientname;')

rows = cur.fetchall()
for row in rows:
    print(row)

# commit and close
con.commit()
con.close()

Output (now with 4 dogs)

# average bill
# (126.5, 'peanut', 'dog')
# (17.0, 'pickles', 'dog')
# (57.75, 'rover', 'dog')
# (19.0, 'snoopy', 'dog')

Useful resources

results matching ""

    No results matching ""