Use FLOOR() (or other SQL functions) in Django queries

Question

In Django 1.2.3 I need to perform a query like this:

SELECT FLOOR(quantity/100) FROM mytable

is there any way to make it in pure Django style?

Then, a more difficult step:

SELECT FLOOR(quantity/100), count(*) 
FROM mytable
GROUP BY FLOOR(quantity/100)

is there any standard solution?

See this: http://stackoverflow.com/a/34622417/908336 – Masood Khaari Jun 06 '16 at 10:08 — Masood Khaari, Jun 06 '16 at 10:08

score 3 · Answer 1 · answered Jan 13 '11 at 09:19

You'll need to use SQL for this. Django's ORM is very useful as far as it goes, but you're stepping well out of the cases that it covers. I wouldn't even say that the raw() queryset method would help here - I would use the functionality to execute custom SQL directly.

score 3 · Answer 2 · answered Jan 17 '11 at 07:46

3

I ended up using the extra clause:

MyModel.objects.extra(group='FLOOR(quantity/100)')\
    .values('group')\
    .annotate(count=Sum('quantity'))\
    .order_by()

the argument of Sum is quite misterious, but it works...

answered Jan 17 '11 at 07:46

Don

16,928
12
63
101

1

Be careful using functions that are database vendor dependent. You can achieve the same result by doing the computation within Django, while remaining database agnostic. You may trade performance for independence however. – Josh Smeaton Jan 17 '11 at 10:23

score 1 · Accepted Answer · answered Apr 02 '19 at 07:42

1

Starting from Django 2.2 you can use the Floor function:

class Floor(Transform):
    function = 'FLOOR'
    lookup_name = 'floor'

answered Apr 02 '19 at 07:42

Dos

2,250
1
29
39

Use FLOOR() (or other SQL functions) in Django queries

3 Answers3