31591

Django: Count of Group Elements

How can we achieve the following via the Django 1.5 ORM:

SELECT TO_CHAR(date, 'IW/YYYY') week_year, COUNT(*) FROM entries GROUP BY week_year;

EDIT: cf. Follow up: Count of Group Elements With Joins in Django in case you need a join.

Answer1:

I had to do something like this recently.

You need to add your week_year column via Django's extra, then you can use that column in the values method.

...it's not obvious but if you then use annotate Django will GROUP BY all of the fields mentioned in the values clause (as described in the docs here https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values)

So your code should look like:

Entry.objects.extra(select={'week_year': "TO_CHAR(date, 'IW/YYYY')"}).values('week_year').annotate(Count('id'))

Recommend

  • Hibernate discriminator column with table per subclass
  • How do I get a positive modulo on a negative dividend
  • Conditional Formatting in VBA, based on functions
  • Find record, that has ALL associated records
  • Access PCF DEV from external machine on same network as host
  • Sql indexes vs full table scan
  • Swift string variables localization
  • How to use ResourceDictionary in Windows Phone class library project
  • Cannot page through all results using nextPageToken on YouTube search API v3
  • C++ Single function pointer for all template instances
  • Can I have a variable number of URI parameters or key-value pairs in Laravel 4?
  • How can I get the choice “H2” back in the H2 consol?
  • How do I shift the decimal place in Python?
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • Query to find the duplicates between the name and number in table
  • Grails calculated field in SQL
  • How to write order and limit within cakephp joins array
  • Custom Tabgroup Appcelerator
  • Test if a set exists before trying to drop it
  • Jetty Server not starting: Unable to establish loopback connection
  • Linq Objects Group By & Sum
  • Read text file and split every line in MSBuild
  • Fetching methods from BroadcastReceiver to update UI
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • How to handle AllServersUnavailable Exception
  • Symfony2: How to get request parameter
  • How to make Safari send if-modified-since header?
  • How to get next/previous record number?
  • GridView Sorting works once only
  • How to pass list parameters for each object using Spring MVC?
  • Android Studio and gradle
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • WPF Applying a trigger on binding failure
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • embed rChart in Markdown
  • JaxB to read class hierarchy