YouTip

## Aggregate Query (aggregate) Aggregate query functions perform calculations on a set of values and return a single value. Before using aggregate queries in Django, you need to import Avg, Max, Min, Count, Sum from django.db.models (capitalized first letter). from django.db.models import Avg,Max,Min,Count,Sum # Import function The return data type of aggregate query is dictionary. The aggregate() function is a terminator clause of QuerySet, generating a summary value, equivalent to count(). After using aggregate(), the data type becomes dictionary, and some APIs of QuerySet data type can no longer be used. DateField can use Max and Min. In the returned dictionary: the default key name is (attribute name plus __aggregate function name), and the value is the calculated aggregate value. To customize the key name in the returned dictionary, you can use an alias: aggregate(alias = aggregate_function_name("attribute_name")) Calculate the average price of all books: ## Example from django.db.models import Avg,Max,Min,Count,Sum # Import function ... res= models.Book.objects.aggregate(Avg("price")) print(res,type(res)) ... !(#) Calculate the count of all books, the most expensive price, and the cheapest price: ## Example res=models.Book.objects.aggregate(c=Count("id"),max=Max("price"),min=Min("price")) print(res,type(res) !(#) * * * ## Grouping Query (annotate) Grouping queries generally use aggregate functions, so before using them, you need to import Avg, Max, Min, Count, Sum from django.db.models (capitalized first letter). from django.db.models import Avg,Max,Min,Count,Sum # Import function **Return value:** * After grouping, if you use values to retrieve data, the return value is a QuerySet data type containing dictionaries; * After grouping, if you use values_list to retrieve data, the return value is a QuerySet data type containing tuples. The limit in MySQL is equivalent to slicing the QuerySet data type in ORM. **Note:** Put aggregate functions inside annotate. * **values or values_list placed before annotate:** values or values_list declares which field to group by, and annotate performs the grouping. * **values or values_list placed after annotate:** annotate means directly grouping by the current table's pk, values or values_list indicates which fields to query, and you need to alias the aggregate function in annotate, and write its alias in values or values_list. ### Prepare Data and Create Models ## models.py class Emp(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() salary = models.DecimalField(max_digits=8, decimal_places=2) dep = models.CharField(max_length=32) province = models.CharField(max_length=32) class Emps(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() salary = models.DecimalField(max_digits=8, decimal_places=2) dep = models.ForeignKey("Dep", on_delete=models.CASCADE) province = models.CharField(max_length=32) class Dep(models.Model): title = models.CharField(max_length=32) Data: ## Execute in MySQL command line: INSERT INTO`app01_emp`(`id`, `name`, `age`, `salary`, `dep`, `province`)VALUES('1', 'Linghu Chong', '24', '6000.00', 'Sales Department', 'Henan'); INSERT INTO`app01_emp`(`id`, `name`, `age`, `salary`, `dep`, `province`)VALUES('2', 'Ren Yingying', '18', '8000.00', 'Guan Gong Department', 'Guangdong'); INSERT INTO`app01_emp`(`id`, `name`, `age`, `salary`, `dep`, `province`)VALUES('3', 'Ren Woxing', '56', '10000.00', 'Sales Department', 'Guangdong'); INSERT INTO`app01_emp`(`id`, `name`, `age`, `salary`, `dep`, `province`)VALUES('4', 'Yue Lingshan', '19', '6000.00', 'Guan Gong Department', 'Henan'); INSERT INTO`app01_emp`(`id`, `name`, `age`, `salary`, `dep`, `province`)VALUES('5', 'Xiaolongnu', '20', '8000.00', 'Guan Gong Department', 'Hebei'); INSERT INTO`app01_dep`(`id`, `title`)VALUES('1', 'Sales Department'); INSERT INTO`app01_dep`(`id`, `title`)VALUES('2', 'Guan Gong Department'); INSERT INTO`app01_emps`(`id`, `name`, `age`, `salary`, `province`, `dep_id`)VALUES('2', 'Linghu Chong', '24', '8000.00', 'Henan', '1'); INSERT INTO`app01_emps`(`id`, `name`, `age`, `salary`, `province`, `dep_id`)VALUES('3', 'Ren Yingying', '18', '9000.00', 'Guangdong', '2'); INSERT INTO`app01_emps`(`id`, `name`, `age`, `salary`, `province`, `dep_id`)VALUES('4', 'Ren Woxing', '57', '10000.00', 'Guangdong', '1'); INSERT INTO`app01_emps`(`id`, `name`, `age`, `salary`, `province`, `dep_id`)VALUES('5', 'Yue Lingshan', '19', '6000.00', 'Henan', '2'); INSERT INTO`app01_emps`(`id`, `name`, `age`, `salary`, `province`, `dep_id`)VALUES('6', 'Xiaolongnu', '20', '8000.00', 'Hebei', '2'); Find the cheapest book price for each publisher: ## Example res = models.Publish.objects.values("name").annotate(in_price = Min("book__price")) print(res) You can see the following output in the command line: !(#) Count the number of authors for each book: