id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
3566	Proposal: ORM aggregation support	Honza Král <Honza.Kral@…>	Russell Keith-Magee	"For origin and some discussion see http://groups.google.com/group/django-users/browse_frm/thread/fc58fbe409a6f098/1110c688e1af58d2

I would like to see an addition to the django ORM - many people has asked for aggregation. Do you think Django should have aggregation support?

I would like to add it in a similar fashion that {{{ ValuesQuerySet }}} is implemented (a subclass to {{{QuerySet}}} with minor changes to how query is constructed and results are returned) - if we add a {{{ AgreggateQuerySet }}} that would:
  * be create from simple {{{ QuerySet }}} by a call to  {{{ aggregate(group_by=(), max=(), min=(), avg=(), sum=(), count=False) }}}, where:
    * {{{group_by}}} is a list of fields on which to aggregate, these will be part of the result, if empty the whole query will be grouped
    * If {{{count}}}, then {{{COUNT(*)}}} will be computed and included
    * {{{sum, max, min}}} and {{{avg}}} will contain a list of fields on which to call {{{SUM(), MAX(), MIN()}}} or {{{AVG()}}} respectively
  * will be a valid queryset
    * {{{filter()}}} will result in limiting the result by appending conditions to the {{{ HAVING }}} clause etc.
    * {{{aggregate()}}} will probably add fields to individual aggregation functions.

{{{ queryset.count()}}} would become identical to {{{queryset.aggregate(count=True)['count']}}}

Example:
{{{
>>> quseryset = Model.objects.all()
>>> queryset.aggregate( group_by=( 'name', 'city' ), sum=( 'pay', 'some_other_field' ), avg=( 'pay', 'age' ), count=True )
[
 {
   'group_by' : { 'name' : 'John Doe', 'city' : 'Prague', }
   'sum' : { 'pay' : 50000, 'some_other_field' : 10000 },
   'avg' : { 'pay' : 10000, 'age' : 30 },
   'count' : 5,
 },
 {
   'group_by' : { 'name' : 'Jane Doe', 'city' : 'Paris', }
   'sum' : { 'pay' : 300000, 'some_other_field' : 10 },
   'avg' : { 'pay' : 100000, 'age' : 32 },
   'count' : 3,
 },
]
}}}

There are a few problems I haven't thought through and would like comments:
  * how to deal with grouping by foreign key? the best seems to include the whole object in the result, not just the ID. (isn't it too magical?)
  * how to specify ordering?
  * how to best present the results? originally I thought that fields in {{{group_by}}} will be on the same level as {{{min}}} or {{{max}}}, but Tim Chase pointed out that it could clash if the model has a field named {{{min}}} .

I will start working on this as soon as I am sure about the interface. Any comments and help would be greatly appreciated."		closed	Database layer (models, ORM)	dev		fixed	orm aggregation group by	nick.lane.au@… jm.bugtracking@… dan.fairs@… lau@… wmealing@… frankie@… alexander.solovyov@… lurker86@… densetsu.no.ero.sennin@… arthur.case@… remco@… martin.paquette@… nicolaslara@… jarthur@… simon@… gajon@… gonz@… marc@… john.syrinek@… jiminy@… nreilly@… cortland@… me@… omat@… kyle.fox@… flosch@… daevaorn@… amlau@… especkman@… kristjan@… robin@… vbmendes@… Marinho Brandão Robin wallenfe@…	Someday/Maybe	1	0	0	0	0	0