Re: How to aggregate values by month

Maybe slightly "off topic" but does anyone have robust strategies for readily switching between different DB-specific SQL code which is (presumably) embedded in one's app/project? (As shown in the two examples presented here) On Nov 5, 1:10 am, David Zhou wrote: > FWIW, on

Re: How to aggregate values by month

FWIW, on Postgres DBs, I've done the following: qs = FooModel.objects.filter(date__gte=start_date, date__lt=end_date).extra(select={'datetrunc': "date_trunc('month', date)"}).values('datetrunc').annotate(total=Sum("value")) date_trunc in postgres also accepts "day" and "week" truncations. -- dz

Re: How to aggregate values by month

Hi Rogério! With django-qsstats-magic it would be something like this: stats = QuerySetStats(Sale.objects.all(), 'date_created') totals = stats.time_series(start, end, 'months', aggregate=Sum('total_value')) averages = stats.time_series(start, end, 'months', aggregate=Avg('total_value')) Great

Re: How to aggregate values by month

Hello Folks! I've got the solution, putting here for future searchs: sales = Sale.objects.extra(select={'month':'month(date_created)','year':'year(date_created)'}).values('year','month').annotate(total_month=Sum('total_value'), average_month=Avg('total_value')) This query works only using

Re: How to aggregate values by month

Hi Sebastien! Thanks for you reply. I'm a newbie on Django and I must confess unfortunately I don't know everything yet ;-). So I saw that you made a snippet regarding about the use of Django Cube. So, where do I put this snippet: at my views.py? Or should I do another class at my models.py?

Re: How to aggregate values by month

Hi Mikhail! Can you give some clue on how to use your plugin considering my scenario? Thanks Rogério Carrasqueira --- e-mail: rogerio.carrasque...@gmail.com skype: rgcarrasqueira MSN: rcarrasque...@hotmail.com ICQ: 50525616 Tel.: (11) 7805-0074 2010/10/28 Mikhail Korobov

Re: How to aggregate values by month

Hi Scott Thanks for you help, unfortunately on trying sales = Sale.objects.filter(date_ created__range=(init_date,ends_date)) .values(date_ created__month) .aggregate(total_sales=Sum('total_value')) This error appeared, global name 'date_created__month' is not defined Do you have

Re: How to aggregate values by month

Hi ! You could also give django-cube a try : http://code.google.com/p/django-cube/ Unlike Mikhail's app, the aggregates are not efficient (because no optimization is made, I am working on this), but this is more than enough if you have a reasonable amount of data (less than millions of rows !!!).

Re: How to aggregate values by month

Hi Rogério, You can give http://bitbucket.org/kmike/django-qsstats-magic/src a try. It currently have efficient aggregate lookups (1 query for the whole time series) only for mysql but it'll be great if someone contribute efficient lookups for other databases :) On 28 окт, 19:31, Rogério

Re: How to aggregate values by month

2010/10/28 Rogério Carrasqueira : > Thanks for you answer. Unfortunatelly I need to output my results on a JSON > file. Do you have any other approach? if the {% regroup %} is what you need, you should know that it's an application of itertools.groupby() --

Re: How to aggregate values by month

Hi Rogério, You can output to json using templates if this is the only thing holding you back from a working solution using regroup. You can use render_to_string with a json template which you can then return with an HttpResponse. It doesn't take advantage of the serialization library but

Re: How to aggregate values by month

Personally I hate writing raw SQL so I would probably try something like this (untested): sales = Sale.objects.filter(date_created__range=(init_date,ends_date)) .values(date_ created__month) .aggregate(total_sales=Sum('total_value')) sales_by_month = [(x.year, x.month, [y for y in sales

Re: How to aggregate values by month

Hello Everybody! I tried this way also: sales = Sale.objects.extra(select={' month': 'month(date_created)'}).filter(date_created__range=(init_date,ends_date)).values('month').aggregate(total_sales=Sum('total_value')) but returned {} Any ideias? Cheers, Rogério Carrasqueira --- e-mail:

Re: How to aggregate values by month

Hi Franklin! Thanks for you answer. Unfortunatelly I need to output my results on a JSON file. Do you have any other approach? Cheers, Rogério Carrasqueira --- e-mail: rogerio.carrasque...@gmail.com skype: rgcarrasqueira MSN: rcarrasque...@hotmail.com ICQ: 50525616 Tel.: (11) 7805-0074

Re: How to aggregate values by month

It may not be a complete answer, but you should know about {% regroup %} just in case: http://docs.djangoproject.com/en/dev/ref/templates/builtins/?from=olddocs#regroup - Franklin -- Art, writing, journal: http://einspruch.com Comics: http://themoonfellonme.com 2010/10/28 Rogério

How to aggregate values by month

Hello! I'm having an issue to make complex queries in django. My problem is, I have a model where I have the sales and I need to make a report showing the sales amount per month, by the way I made this query: init_date = datetime.date(datetime.now()-timedelta(days=365)) ends_date =