CODEWITHSUNDEEP
djangoorm
Abinash Biswal
Abinash Biswal

Reputation: 63

How to use both Count() and Min/Max in sql

I have the following table: MyTable

   name   | price
|-------------------|
|   a     |    10   |
|-------------------|
|   b     |     5   |
|-------------------|
|   a     |     7   |
|-------------------|
|   a     |     3   |
|-------------------|
|   a     |     12  |
|-------------------|
|   b     |     6   |
|-------------------|
|   c     |     2   |
|-------------------|
|   c     |     5   |
|-------------------|

I want to count the frequency of the name and need to get the max_price and min_price for each name.

The expected output is:

   name   | count   | min_price | max_price
|-------------------|----------------------|
|   a     |     4   |  3        |   12     | 
|-------------------|----------------------|
|   b     |     2   |  5        |   6      |
|-------------------|----------------------|
|   c     |     2   |  2        |   5      |
|-------------------|----------------------|

I would like to write Django ORM query. Please help me to achieve it.

Upvotes: 1

Views: 180

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477437

You can annotate the items with:

from django.db.models import Count, Max, Min

MyModel.objects.values('name').annotate(
    count=Count('pk'),
    min_price=Min('price'),
    max_price=Max('price')
).order_by('name')

Upvotes: 2

Related Questions