OData v4 groupby with $count

Question

I'm using OData v4 with WebAPI with these nuget-packages:

Microsoft.Data.OData 5.7.0

Microsoft.AspNet.WebApi.OData 4.0.30506

I can't get groupby to work with count. It should be the most simple thing.

I have a simple table/entity called Delivery. It has a status column (among several other ones). What I basically want to do is this SQL query, but with OData:

SELECT e.status, count(*) AS total
FROM Delivery e
GROUP BY e.status

I've tried using countdistinct, but it doesn't give me the correct results.

/odata/deliveries?$apply=groupby((status),aggregate(status with countdistinct as total))

It returns:

"value": [
{
    "@odata.id": null,
    "total": 1,
    "status": 1
},
{
    "@odata.id": null,
    "status": 2,
    "total": 1
},
{
    "@odata.id": null,
    "status": 4,
    "total": 1
}
]

The correct results should be (which is what my SQL-query returns):

status  total
1       2
2       22
4       1

I've also read about the virtual property $count, but it seems as Microsoft doesn't support it yet.

How do I use group by together with a simple count with OData v4?

Solution 1

Whilst $count is not supported, contdistinct has been supported for a while and is possibly more syntactically correct for OData aggregate scenarios, the documentation is a little bit lacking but countdistinct gives the caller control over which field to use in the result set to determine the unique results. This is especially useful in complex nested or expanded grouping queries.

So it turns out you have a simple bug in your query!

In your query: /odata/deliveries?$apply=groupby((status),aggregate(status with countdistinct as total))

You are asking for count all the distinct values of status. Because you are grouping by status, the distinct values in each group will always be 1 :)

Your actual query should be to countdistinct over a property that will be unique for each item in the set, usually an Id. Assuming Delivery has an Id field called "Id", your query should be this:

/odata/deliveries?$apply=groupby((status),aggregate(id with countdistinct as total))

this will translate closely to your expected SQL:

SELECT status, COUNT(DISTINCT id) AS total
FROM Delivery
GROUP BY status

Solution 2

Currently in ODATA v4, it supports the requirement which you have

/Entity/$apply=groupby((status),aggregate($count as COUNT))

The above code translates to the following

select status, count(*) from  entity group by status;

Comments

[Nameless]

can any filter or group be applied on the results of odata? Say, I want to select only those who have total as 2. Is it even possible?

[Chris Schaller]

You can $filter the group by result by appending your query with &$filter=total eq 2 but that is less useful, it wont return all the items that satisfy the criteria, only the groups that do.