[FIXED] How do I count() only the distinct values on a specific field when returning a queryset

Issue

I have a function that returns a queryset that is a list of records that I would like to return the count() of the records which it does BUT I want to only count the distinct values from a specific field. I can easily do this in a SQLite query but cant seem to get it in Django.

Here is the SQLite Query that works BUT I need to do this in a Django View:

SELECT DISTINCT Host FROM inventory_scandata WHERE Project_Assigned LIKE '%Hooli%' AND Environment LIKE '%PROD%'

Here is my current function that returns the non-distinct count:

def HooliProject(request):
    prodAssets = ScanData.objects.filter(Q(Project_Assigned__icontains="Hooli_1") & (Q(Environment__icontains="PROD")))
    prodAssetsHosts = prodAssets.distinct()
    prodAssetsDistinct = prodAssetsHosts.distinct()
    prodAssetsCount = prodAssetsDistinct.count()
    context = {
        'prodAssets': prodAssets,
        'prodAssetsCount': prodAssetsCount
        }

    return render(request, 'HooliProject.html', context)

Solution

If all you want to do is count() the distinct values of Host field, you can use values('Host').distinct().count(), so you're using distinct() in conjunction with values().

ScanData.objects.filter(
    Q(Project_Assigned__icontains="Hooli_1") & Q(Environment__icontains="PROD")
).values('Host').distinct().count()

Below is your code with the additions where needed.

def HooliProject(request):
    prodAssets = ScanData.objects.filter(Q(Project_Assigned__icontains="Hooli_1") & (Q(Environment__icontains="PROD")))
    prodAssetsHosts = prodAssets.values('Host') # <--- I've changed here ;D
    prodAssetsDistinct = prodAssetsHosts.distinct()
    prodAssetsCount = prodAssetsDistinct.count()
    context = {
        'prodAssets': prodAssets,
        'prodAssetsCount': prodAssetsCount
        }

    return render(request, 'HooliProject.html', context)

Also if you're on PostgreSQL you can use distinct('Host').count().

On PostgreSQL only, you can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query. Here’s the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names.

Answered By - Bernardo Duarte