shabda
Comments
Reactions

Django aggregation tutorial

By : Shabda Raaj

One of the new and most awaited features with Django 1.1 was aggregation. As usual, Django comes with a very comprehensive documentation for this. Here, I have tried to put this in how-to form.

Jump to howtos or Get source on Github.

Essentially, aggregations are nothing but a way to perform an operation on group of rows. In databases, they are represented by operators as sum, avg etc.

To do these operations Django added two new methods to querysets.

  1. aggregate
  2. annotate

When you are have a queryset you can do two operations on it,

  1. Operate over the rowset to get a single value from it. (Such as sum of all salaries in the rowset)
  2. Operate over the rowset to get a value for each row in the rowset via some related table.

The thing to notice is that option 1, will create one row from rowset, while option 2 will not change the number of rows in the rowset. If you are into analogies, you can think that option 1 is like a reduce and option 2 is like a map.

In sql terms, aggregate is a operation(SUM, AVG, MIN, MAX), without a group by, while annotate is a operation with a group by on rowset_table.id. (Unless explicitly overriden).

Ok enough talk, on to some actual work. Here is a fictional models.py representing a HRMS application. We will use this to see how to use aggreagtion to solve some common problems.

from django.db import models

class Department(models.Model):
    dept_name = models.CharField(max_length = 100)
    established_on = models.DateField()

    def __unicode__(self):
        return self.dept_name

class Level(models.Model):
    level_name = models.CharField(max_length = 100)
    pay_min = models.PositiveIntegerField()
    pay_max = models.PositiveIntegerField()

    def __unicode__(self):
        return self.level_name

class Employee(models.Model):
    emp_name = models.CharField(max_length = 100)
    department = models.ForeignKey(Department)
    level = models.ForeignKey(Level)
    reports_to = models.ForeignKey('self', null=True, blank=True)

    pay = models.PositiveIntegerField()
    joined_on = models.DateField()

class Leave(models.Model):
    employee = models.ForeignKey(Employee)
    leave_day = models.DateField()

"""
#Populate DB, so we can do some meaningful queries.
#Create Dept, Levels manually.
#Get the names file from http://dl.getdropbox.com/u/271935/djaggregations/names.pickle
#Or the whole sqlite database from http://dl.getdropbox.com/u/271935/djaggregations/bata.db
import random
from datetime import timedelta, date
import pickle
names = pickle.load(file('/home/shabda/names.pickle'))
for i in range(1000):
    emp = Employee()
    emp.name = random.choice(names)
    emp.department = random.choice(list(Department.objects.all()))
    emp.level = random.choice(Level.objects.all())
    try: emp.reports_to = random.choice(list(Employee.objects.filter(department=emp.department)))
    except:pass
    emp.pay = random.randint(emp.level.pay_min, emp.level.pay_max)
    emp.joined_on = emp.department.established_on + timedelta(days = random.randint(0, 200))
    emp.save()
"""

"""
employees = list(Employees.objects.all())
for i in range(100):
    employee = random.choice(employees)
    leave = Leave(employee = employee)
    leave.leave_day = date.today() - timedelta(days = random.randint(0, 365))
    leave.save()

"""

Find the total number of employees.

In sql you might want to do something like,

select count(id) from hrms_employee

Which becomes,

Employee.objects.all().aggregate(total=Count('id'))

If fact doing a connection.queries.pop() shows the exact query.

SELECT COUNT("hrms_employee"."id") AS "total" FROM "hrms_employee"

But wait, we have a builtin method already for that, Employee.objects.all().count(), so lets try something else.

Find the total pay of employees.

The CEO wants to find out what is the total salary expediture, this also converts the queryset to a single value, so we want to .aggregate here.

Employee.objects.all().aggregate(total_payment=Sum('pay'))

Gives you the total amount you are paying to your employees.

Find the total number of employees, per department.

Here we want a value per row in queryset, so we need to use aggregate here. Also, there would be one aggregated value per dpeartment, so we need to annotate Department queryset.

Department.objects.all().annotate(Count('employee'))

If you are only interested in name of department and employee count for it, you can do, Department.objects.values('dept_name').annotate(Count('employee'))

The sql is

SELECT "hrms_department"."dept_name", COUNT("hrms_employee"."id") AS "employee__count" FROM "hrms_department" LEFT OUTER JOIN "hrms_employee" ON ("hrms_department"."id" = "hrms_employee"."department_id") GROUP BY "hrms_department"."dept_name"

Find the total number of employees, for a specific department.

Here you could use either of .annotate or .aggregate,

Department.objects.filter(dept_name='Sales').values('dept_name').annotate(Count('employee'))
Department.objects.filter(dept_name='Sales').aggregate(Count('employee'))

If you see the SQLs, you will see that .annotate did a group by, while the .aggregate did not, but as there was only one row, group by had no effect.

Find the total number of employees, per department, per level

This time, we can not annotate either Department model, or the Level model, as we need to group by both department and level. So we will annotate on Employee

Employee.objects.values('department__dept_name', 'level__level_name').annotate(Count('id'))

This leads to the sql,

SELECT "hrms_department"."dept_name", "hrms_level"."level_name", COUNT("hrms_employee"."id") AS "id__count" FROM "hrms_employee" INNER JOIN "hrms_department" ON ("hrms_employee"."department_id" = "hrms_department"."id") INNER JOIN "hrms_level" ON ("hrms_employee"."level_id" = "hrms_level"."id") GROUP BY "hrms_department"."dept_name", "hrms_level"."level_name

Which combination of Employee and Deparments employes the most people

We can order on the annotated fields, so the last query is modified to,

Employee.objects.values('department__dept_name', 'level__level_name').annotate(employee_count = Count('id')).order_by('-employee_count')[:1]

Which employee name is the most common.

We can want to group by emp_name, so emp_name is added to values. After that we order on the annotated field and get the first element, to get the most common name.

Employee.objects.values('emp_name').annotate(name_count=Count('id')).order_by('-name_count')[:1]


This was a overview of how django annotations work. These remove a whole class of queries for which you had to use custom sql queries in the past.


Resources

  1. Source on Github
  2. sqlite file for this model to test
  3. Aggregation on Django docs

Want to build a Django app? Talk to us


Related Posts


Can we help you build amazing apps? Contact us today.

Comments

Brandon Konkle

Thank you very much for the detailed overview! I've been looking forward to aggregation because of some challenges in a project towards the beginning of this year. This is definitely a great new feature for Django.

commmenttor
Vitaly Babiy

Love your posts, Another great post.

commmenttor
shabda

@walter: Thanks very much. I would love to see more translations. All the content here is CC-BY-SA licensed. I should put up a notice for that somewhere.

commmenttor
Jeff 18th Aug., 2009

Thanks for the tutorial.

commmenttor
Steve Roger

Hi..Thanks for an article on Django.Presently I am working on a couple of Database projexts and really it was a tough task for me to perform aggregation.Now i found a solution for it..

commmenttor
Walter Cruz

Great, I was in doubt about the license ;)

commmenttor
Walter Cruz

IN your links, bitbucket is .org, don't .com ;)

commmenttor
shabda

@Walter: Thanks so much. Changed it now.

commmenttor
spooky 20th Aug., 2009

I have a question, what if I want to get an aggregate that is a result of an arithmetic operation on two columns?
eg. sql would be

select foo*bar from buz

do I have to have a custom sql or is there some way to do this using the api? (some sort of Buz.objects.aggregate('foo*bar') )

commmenttor
spooky 20th Aug., 2009

correction
select sum(foo*bar) from buz
was the thing I wanted to write ;)

commmenttor
Charles

Great work - thanks for the post!

commmenttor
Danilo Cabello

In the last query, I think the Count should be with emp_name, not with id.

Great article!

commmenttor
martin 13th Nov., 2009

Hi Shabda, thanks for the great tutorial! I have one scenario that i can't seem to solve with one of the examples presented, i posted it on stackoverflow here. Any chance i could persuade you to have a look at it?
Thanks!
Martin

commmenttor
Django developer

Nice tutorial. I just bookmarked in my browser for my development.

commmenttor
joujou 12th Sept., 2013

Hello,
thank you for your post!

I have the same model:

class Employee(models.Model):
emp_name = models.CharField(max_length = 100)
department = models.ForeignKey(Department)
level = models.ForeignKey(Level)
reports_to = models.ForeignKey('self', null=True, blank=True)

the prob is: in model form admin, the dropdown list of reports_to don't display list of employee name but "employee object, employee object"!!
how can i fix that?

commmenttor
Akshar Raaj 12th Sept., 2013

@joujou:
Guess you are missing __unicode__ on Employee.

commmenttor
What's Lastest New in Men's Nike Air Max Shoes?

Django aggregation tutorial - Agiliq Blog | Django web app development

commmenttor
Trend Sunglasses Wholesale- Jazz Up Your Boring Living

Django aggregation tutorial - Agiliq Blog | Django web app development

commmenttor
gucci outlet

I recently fitted often the application. I renamed the "first cd, micron assigned the item a new username and password, after that attemptedto import several examination photographs from the camera move. After a tiny possibly even, the actual application crashed. My partner and i opened it up less difficult. My partner and i clicked on often the cd (to see if the actual pictures had brought in or maybe not). While i get into the username and password, the whole software package dives. In the beginning, I believed I would have typed typically the security password improperly, but really performed the same thing regarding ten times at this point. Do they offer a correct for this?
gucci outlet http://www.iphra.info

commmenttor
gucci outlet

Sherri Now i'm looking for a method to consider photos offmy new iphone 4 photo burial container make these about mymy i pad2 picture burial container help I actually didn't remember my very own pass word in addition to ended up being thinking when there had been at any rate in order to reset button that and when I will put in dome safety measures inquiries in case it happens once more I don't want to lose this photos within it so please assist me to SINCE thanks a lot One more thing I've seen is obviously which for most people, less-than-perfect credit rating will be the response to situation above their very own manage. For example they might actually are saddled by having an health issues so they genuinely have abnormal costs going to choices. It will be caused by a occupation damage or maybe lack of ability to function. Oftentimes divorce as well as separation can definitely send out the actual economical circumstances inside the nuisance way. Cheers discussing your opinions about this web page.
gucci outlet http://www.gandsappaloosas.com/guccioutelt.php

commmenttor
buy Gucci beanie doo

Good morning! can you please assist me to to enjoy a ym detector scanning device? Anyone utilized my acct and can detector scanner identify when the cyber-terrorist additionally watching my wife's cam when all of us are chatting? thanks a lot!

commmenttor
beats pro review film

My spouse and i changed the security password instantly while i identified vizgin was obviously a rip-off... We cautioned all the individuals on my buddy checklist about it. It acquired sent to myself via an individual in the Yahoo pal checklist... they were closed away from. I really hope they didn't receive scammed. Their the shame men and women would certainly do that.

commmenttor
cheap authentic canada goose jackets

My partner and i rarely depart an answer, nonetheless i did a couple of browsing and wound up the following Fran Battles Rear » Show Your Tale. And that i possess different concerns for yourself if you usually do not imagination. Could it be solely myself as well as can it appear like several of the remarks seem like they can be authored by head lifeless visitors? As well as, for anyone who is posting in supplemental online sites, Let me adhere to anything new you will need to post. Will you make a list in the complete web addresses on your provided internet sites like your Facebook site, tweets nourish, as well as linkedin report?

commmenttor
canada goose authentic online retailers

Cheers Brian for a wonderful suggestion, also non-developers just like us must be able to draw this specific down: )This is very necessary for a multi-international website with regards to SEO.

commmenttor
louis vuitton bags aaa

Well, the particular apple ipad tablet certainly lacks several vital capabilities. Nevertheless honestly I do think Apple mackintosh does that deliberately so that throughout a few months or so they might merely release a fresh in addition to current apple ipad tablet. They can be a very wise business and get plans to get anything. . -= Url Building's final site... Purchase. edu Backlinks: How to Get. edu Inbound links via. edu Sites =-.
louis vuitton bags aaa http://www.nrta.net/louis-vuitton-bags/louis-vuitton-bags-aaa.html

commmenttor
gucci outlet

Array auction how one can goFriday Reflections BY B. P. Sidhu
gucci outlet http://rcbti.com/cheapguccioutlet.html

commmenttor
canada geese food gift baskets canada

I highly are in agreement with the foundation on your predicament using the RIAA. $1M for 7 music is ridiculous. How do they have got obtained apart using this looting intended for that long? I seriously wish & wish which you are all successful in cases like this and that the the courtroom proceedings are generally reside and so the RIAA could be uncovered for the Shylocks that they and their lawyers/advisers are.

commmenttor
shop moncler italia

Most of us employ ESX5 having Home windows Storage area Storage space ISCSI targets without issues. I suppose "mileage may possibly vary" does apply here.

commmenttor
uggs outlet

I'm considering utilizing microsoft windows safe-keeping machine, However , I use learn sumwhere on the web dat VMWare can not gain access to often the VHD document this home windows provide... are these claims real? please simplify vSphere your five doesn't like the iSCSI spots that will House windows storage space web server offers.

commmenttor
christian louboutin outlet

There was a time when i would utilize Dropbox these days I favor to make use of Tonido. Instead of syncing data in addition to generating identical copies to each system/device, I just employ theTonido app in the iPhone/iPad, and also grab any file I'd like at any time by everywhere, providing our always-on multimedia systems center technique continues on the internet. I will actually steady stream any cut DVD MOVIE with VLC in the new iphone 4. I am able to actually permit others to get easy access by merely opening up any internet browser along with stepping into any security password in the "page".

commmenttor
christian louboutin outlet

The issue occur to be working on are generally your personal pre-conceived images of the tools (tablets, etc)
christian louboutin outlet http://www.acukaizen.com/Christian-Louboutin-Outlet.html

commmenttor
Michael Kors Outlet

Awesome, and it is functioning? I used to be receiving all sorts of iSCSI period outs if we improved some sort of close friends things to five. 0... but it really ended up being a young release associated with 5 various. zero might be they get given that set the item.
Michael Kors Outlet http://www.hdmatch.com

commmenttor
dre beats wholesale 99 cents

Unique article: Typically the your five Finest SEO Hints You retain Ignoring | Vertical Measures Worthwhile posting David. The particular write-up turned to be considerably more beneficial than My partner and i likely even though pressing the link with BlogEngage.

commmenttor
Louis Vuitton Sunglasses

There is also a web-site inside development right this moment referred to as BEATPLAY which will be a useful gizmo regarding independent designers to display their own songs totally free. It is great way to achieve publicity along with followers without having promoting your own internal into a record ticket. Check out beatplay. wordpress. net right this moment with regard to announcement on up-and-coming music artists plus the progress the particular internet site. BEATPLAY, IT'S GOING TO GENUINE GOOD... ABOUT THE AUTHENTIC.
Louis Vuitton Sunglasses http://www.ogdworld.com/louis-vuitton-sunglasses/

commmenttor
chaussures nike air max pas cher

4, peut-être enceinte en Août du mamelon de la traite et de l'aréole, si jaune extrusion de liquide, sa composition et la production de colostrum au cours des cinq premiers jours de la même conduit sur la perméabilité des signes de la main-d'œuvre, les mères que vous avez à comprendre! ! Quels sont les signes de la main-d'œuvre Les babillards et de leurs familles afin de permettre une préparation adéquate à l'accouchement, il est nécessaire de comprendre les signes de la main-d'œuvre. Babillards dans le travail lorsque les trois principaux signaux: voir rouge, des douleurs, de l'eau cassé.

commmenttor
gucci outlet

Basically advice! A person responded to the problem I submitted to StackOverflow.
gucci outlet http://www.soundviewgc.com/gucci-outlet.php

commmenttor
Louis Vuitton Outlet

insufficient multi-tasking is the major quick approaching this device, nonetheless simply no HIGH DEFINITION is incredibly very much estimated and thus isn't any photographic camera, even though with all these types of capabilities missing am certain that numerous would nonetheless get the item...
Louis Vuitton Outlet http://www.primecarems.com

commmenttor
clothing canada news zimbabwe latest

Thank you for all the information My partner and i to obtain eliminated often the Heavens by simply high speed. I have already been blaming this isp to the bandwidth complications and will surely have to be able to appologise.

commmenttor
canada goose parka

A fantastic music site, in my opinion, is a who has ANYTHING, and that includes the tunes. I have inspected every single web site, the one two that are really worth the time are 8tracks and also Grooveshark.

commmenttor
canada goose outlet

Well ipad device is actually a elegant gadget nevertheless. However frankly simply a waste material of your hard-earned money . -= NpXp's very last blog site... Exactly how To not Obtain Restricted By Google Adsense =-.
canada goose outlet http://www.koncreteboxing.com/canadagooseoutlet.html

commmenttor
cheap nfl jerseys

spotify is the greatest i have at any time employed and i've employed almost all. problem is a possibility designed for the actual U. S i9000. should you have almost any pals existing outside of the United. T., keep these things call and make an accounts and allow you the facts. is often a headache, however worthwhile.
cheap nfl jerseys http://greensburgracquetclub.com/cheap-nfl-jerseys.php

commmenttor
Cheap Swords Sets

Exp茅dition rapide superbe ! Great seller! Article exactement comme d茅crit ! Merci ! !

commmenttor
Sakabato Japanese Sword

belle chaussure , expédition rapide , merci beaucoup

commmenttor
Samuria Swords Sets

Parfait transaction ! Arrivé rapidement et produit comme promis !

commmenttor
louis vuitton neverfull

spotify is best we have ever previously employed in addition to we've utilized many. issue is difficult intended for the particular Ough. S i9000. in case you have almost any close friends existing outside the United. Nasiums., you can keep them make an bank account and provide the data. could be a inconvenience, yet well worth it.

commmenttor
canada goose black friday

Hi there, I'm the actual digi person. Why recognized promotion just valid till 31st august? Why not consider to people who overlook this specific opportunity?

commmenttor
Louis Vuitton Outlet

sirsome cells are definitely not editable. just how can many of us modify all of them Their Great Program,,,,, Its very useful. Pls suggest to get editing and enhancing the choice of "Person accountable for Sighning".
Louis Vuitton Outlet http://mobilvegas.com

commmenttor
Canada Goose Parka

Apple company turned down often the revise this morning a result of the approach i-tunes Syncing had been made it possible for. Nevertheless , No later than this repair it today as well as resubmit often the upgrade in order to apple mackintosh. Hopefully it can be accepted shortly.
Canada Goose Parka http://pdrequipment.com

commmenttor
Moncler Black Friday

Graphics are one of the nearly all neglected facets of a web site. Perpetual entire SEARCH ENGINE MARKETING component of the item along with think of the particular window blind, explanation we are these kinds of. Now just think the fact that bumblebees are usually window blind too and cannot learn thoughts throughout photos this well. We know they will evaluate the item to get shade and can find persons.

commmenttor
canada goose outlet

I skepticism there are actually ten individuals in this particular nation who'd have got selected with regard to Romney apart from the fact that he / she has never launched his / her taxes and tax returns. Now i am using Romney on this a single.
canada goose outlet http://www.advancedstoves.com

commmenttor
Louis Vuitton Outlet

My partner and i inspected stumpleaudio and found new great groups, however I am going to stick to Grooveshark for the time being. Individually I think it's better than from any of the other out there.
Louis Vuitton Outlet http://louisvuittonjpbags.com

commmenttor
fit flops eBay

Florida C Choice Kosttilskud har modtaget flotte anmeldelser blandt fitness-eksperter og vægttab konsulenter siden lanceringen tre af deres meget effektive vægttab supplerer: Garcinia Cambogia Plus, 100% Pure GCA Green Coffee Bean Extract og Hindbær keton Ultra?? ?. Disse produkter har vundet popularitet i sundheds-og wellness samfund på grund af deres effektivitet i at hjælpe folk tabe sig. De er også billige, ikke har nogen bivirkninger, og er lavet med 100% naturlige ingredienser.? Choice Kosttilskud er en virksomhed fokuseret på at producere helseprodukter, der har vist videnskabelige resultater og er parate efter den højeste sundheds-og sikkerhedsstandarder. Choice Kosttilskud er også meget aktiv i at hjælpe enkeltpersoner overvinde sundhedsspørgsmål især med hensyn vægttab. Deres produkter er i verdensklasse kvalitet og anses for at være en revolution i sundheds-og wellness industrien Her er en erklæring fra en af ​​Choice Kosttilskud klienter:.?? Jeg har taget Garcinia Cambogia Plus for en kort tid, og jeg ? m allerede begyndt at se en ændring i min energi niveau og har mistet et par pounds. Jeg er fascineret af dette produkt og lide, at det indeholder calcium og kalium og er lavet af naturlige ingredienser. Jeg har mistet et par inches af mave fedt, og jeg? M ser frem til at miste endnu mere. Oplysningerne med produktet var let at forstå og informativ. Pillerne er lette at sluge og har ingen eftersmag.? ? C Scotty? Om Choice Kosttilskud? Choice Kosttilskud er en af ​​de øverste producenter af 100% naturlige helseprodukter, som er lavet til at støtte i at fremme en sund livsstil ved at forebygge sygdom og forbedre kroppens? S vitale funktioner. Choice Kosttilskud bruger kun yderst effektive og sikre naturlige ingredienser for at sikre optimal sundhed.

commmenttor
handbag

Love the purse. Colors are great. Reasonable shipping time

commmenttor
cheap replica givenchy handbags

Thank you! Highly recommend! Wonderful hipster and great pattern!!

commmenttor
Louis Vuitton Outlet

Hmmmm! oh yeah it is as if it were addon will it be... Costly! damn.. all their accessories usually are damn darn costly. -= Chethan's previous website... 10 Great Top features of Ubuntu twelve. apr. LTS Launch in The spring 30 =-.
Louis Vuitton Outlet http://www.gorgeousgrey.com

commmenttor
nike air max leather

Tremendous things here. I'm very glad to look your post. Thank you a lot and I am having a look ahead to contact you. Will you kindly drop me a mail?

commmenttor
hermes kelly bag knockoff

hermes kelly bag knockoff hermes belt replica difference item ttubik

commmenttor
Givenchy bags Outlet Store

Thank you so much!! Very nice purchase experience.

commmenttor
Louis Vuitton Outlet

We have just about everything We can think of and i also additionally had a pair of some other specialists give it a go. anything runs excellent unless you try and install a think COMPUTER ITSELF. For switches my spouse and i possibly experimented with setting up some sort of guests unix. I use quit during this period and that i am at the moment utilizing an iSCSI targe computer software through KernSafe. this is actually the very first time There are tried out iStorage through Kernsafe... remorseful, i no longer indicate to call decline, We have not any fascination with these. With a Vivid be aware I could see any thread anywhere however among the typically the VM your five. back button, Microsoft iSCSI issue so they really tested out the actual MASTER OF SCIENCE Server 8 Designer model featuring a iSCSI targeted computer software and yes it do the job fantastic, although that does not assist me to at the moment in creation.
Louis Vuitton Outlet http://www.yeoldebicycleplace.com

commmenttor
Moose Knuckles Stirling Parka Men

Super fast shipping! Smooth transaction! Thank you!

commmenttor
Discount Moose Knuckles Ballistic Bomber Men Jackets Sale

Love my shoe! Very happy with this transaction.! Thanks Much!

commmenttor
replica mulberry bags spain

Love it! Great Seller, fast ship!

commmenttor

Reactions

tek_news

Reddit/p: Django aggregation tutorial http://bit.ly/9BzOs

This comment was originally posted on Twitter

howsthedotcom

Django aggregation tutorial http://su.pr/2VqY8V

This comment was originally posted on Twitter

uswaretech

http://bit.ly/nqIDs New blog post

This comment was originally posted on Twitter

webstartupgroup

StartupNews: Django aggregation tutorial http://bit.ly/hWS54

This comment was originally posted on Twitter

bcurtu

Django aggregation tutorial http://bit.ly/5Q5Fy

This comment was originally posted on Twitter

uswaretech

@twoodham May I suggest http://bit.ly/5Q5Fy

This comment was originally posted on Twitter

shabda

@apgwoz Employee.objects.values(’name’).annotate(Count(’id’)) -> select count(id) from emp group by name http://bit.ly/nqIDs

This comment was originally posted on Twitter

delicious50

Django aggregation tutorial http://bit.ly/CLtMh django

This comment was originally posted on Twitter

© Agiliq, 2009-2012