Search code examples
pythondjangocountmodels

Count data from a table excluding duplicates

I need to count elements from a table. The problem is that I do have a solution for this, but I'm using a function that deletes duplicates from the table and then I can get the count without issue (but it takes a while to process) This is an example of my model/table:

enter image description here

If I do a count from this I will get the following result: 3 Shipped, 4 allocated, 2 No status, 1 Loaded.

What I need is to get the following result: 2 Shipped (ignoring 1 duplicate), 1 Allocated (ignoring 3 duplicate), 2 No status(There is no duplicate), 1 Loaded.

Thanks for your time,

Solution

  • If you are using PostgreSQL you can use the following to use DISTINCT ON

    Model.objects.order_by('field').dictinct('field').count()

    If you are using MySQL you can use

    Model.objects.order_by('field').aggregate(count=Count('field', distinct=True))['count']

    You need to order_by the field you pass to distinct, there is more information in the docs