american national standards institute (ansi) structured query language (sql) standard defines many sql functions.

aggregate functions like the following are useful in peewee.

avg() - returns the average value.
count() - returns the number of rows.
first() - returns the first value.
last() - returns the last value.
max() - returns the largest value.
min() - returns the smallest value.
sum() - returns the sum.

in order to implement these sql functions, peewee has a sql helper function fn(). in above example, we used it to find count of records for each city.

following example builds a select query that employs sum() function.

using bill and item tables from models defined earlier, we shall display sum of quantity of each item as entered in bill table.

item table

the item table with the data is given below −

id	item name	price
1	laptop	25000
2	printer	12000
3	router	4000

bill table

the bill table is as follows −

id	item_id	brand_id	quantity
1	1	3	5
2	2	2	2
3	3	4	5
4	2	2	6
5	3	4	3
6	1	3	1

example

we create a join between bill and item table, select item name from item table and sum of quantity from bill table.

from peewee import *
db = sqlitedatabase('mydatabase.db')

class basemodel(model):
   class meta:
      database = db

class item(basemodel):
   itemname = textfield()
   price = integerfield()

class brand(basemodel):
   brandname = textfield()
   item = foreignkeyfield(item, backref='brands')

class bill(basemodel):
   item = foreignkeyfield(item, backref='bills')
   brand = foreignkeyfield(brand,      backref='bills')
   qty = decimalfield()

db.create_tables([item, brand, bill])

qs=bill.select(item.itemname, fn.sum(bill.qty).alias('sum'))
   .join(item).group_by(item.itemname)
print (qs)
for q in qs:
   print ("item: {} sum: {}".format(q.item.itemname, q.sum))

db.close()

above script executes the following select query −

select "t1"."itemname", sum("t2"."qty") as "sum" from "bill" as "t2" 
inner join "item" as "t1" on ("t2"."item_id" = "t1"."id") group by "t1"."itemname"

output

accordingly, the output is as follows −

item: laptop sum: 6
item: printer sum: 8
item: router sum: 8

Save or share this article