Group and Sum

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

I originally thought I would select records and programmatically sum values of a group using python. However, you can achieve the same result by summing on groups inside an SQL statement.

This example starts by adding another table to a database (gitbook.db) built for previous examples. The database now has three tables; animals, patients, and vet_visit. In the following code I select a patient (rover) and I sum the total amount of money spent on vet visits.

Add table containing vet visits

import sqlite3

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

# cursor
cur = con.cursor()

# create visit table
cur.execute('CREATE TABLE vet_visit (visitid INTEGER PRIMARY KEY AUTOINCREMENT,\
             visitpatientid INTEGER, visitanimalid INTEGER, visitprice REAL,\
             FOREIGN KEY(visitpatientid) REFERENCES patient(patientid),\
             FOREIGN KEY(visitanimalid) REFERENCES animals(animalid));')

# data
visits = [(1, 1, 35.00, ), 
          (1, 1, 35.00, ), 
          (2, 2, 43.00, ), 
          (1, 1, 126.00, ), 
          (2, 2, 43.00, ), 
          ]

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

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

GUI

Query and sum records

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

# cursor
cur = con.cursor()

# execute
# QUERY the sum of all visits for 'rover' (return 1 line; e.g. 'rover' '196.00')
cur.execute('SELECT patient.patientname, SUM(vet_visit.visitprice) FROM patient \
             JOIN vet_visit ON vet_visit.visitpatientid == patient.patientid \
             WHERE patient.patientname = "rover"\
             GROUP BY patientname;')

rows = cur.fetchall()
print(rows)

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

Output

# [('rover', 196.0)]