How to get count in OData

[Former Member]

Hi All,

Is there anyway in SAP HANA OData Services to get count or distinct count ? eg. to achieve something of

select group_type, count(customer_id), sum(sales_price) from "SOME_TABLE" group by group_type;

[former_member98096 · Accepted solution]

Hi Hriday,

there is an URL parameter $count, which can be used to directly retrieve the size of an EntitySet or Association. Just use it like that:

• http://<your-service>/<some-path>/<your-entity-set>/$count
• http://<your-service>/<some-path>/<your-entity-set>/<your-navigation-property>/$count

Are you looking for something like this?

Best,

René

[Former Member · Accepted solution]

Thanks Rene,

I've explored the $count, but thats not what I'm looking for. Let me give a proper example.

Group_Type	Customer_id	Amount
Group 1	1	10
Group 2	2	20
Group 3	3	20
Group 1	4	30
Group 1	5	30
Group 2	6	20
Group 3	7	10

Now when I'll run the query,

select group_type, count(customer_id), sum(sales_price) from "SOME_TABLE" group by group_type;

it will return something like

Group Type	Count(Customer_id)	Sum(Amount)
Group 1	3	70
group 2	2	40
Group 3	2	30

Hope this clears my doubt.

Thanks

Hriday

[former_member98096 · Accepted solution]

Hi Hriday,

I see. I'm not sure if there is a way to express this in the OData service description (to be honest, I doubt it). I propose to create a view, which builds the aggregates as you want, and expose this view via an OData service.

Best,
René

[Former Member · Accepted solution]

Hi Rene,

Thats what I'm doing as a workaround. I've created a dummy col and assigned its value as 1 and doing sum over it.

But still felt that count is one of the basic aggregate function, so don't know why SAP HANA doesn't support it in OData services.

Thanks

Hriday

[former_member329609]

Hello Hriday,

Please find this section from the SAP HANA dev guide of SP06. (page 295)

Explicit Aggregation

The example for the explicit aggregation is based on the following table definition: sample.odata:revenues.hdbtable

table.schemaName = "ODATASAMPLES";table.tableType = COLUMNSTORE; table.columns = [ {name = "Month"; sqlType = INTEGER; nullable = false;}, {name = "Year"; sqlType = INTEGER; nullable = false;}, {name = "Amount"; sqlType = INTEGER; nullable = true;} ];table.primaryKey.pkcolumns = ["Month","Year"];

You can aggregate the columns of objects (without metadata) that are necessary for the derivation of aggregation by explicitly denoting the column names and the functions to use, as illustrated in the following example of a service definition: sample.odata:aggrexpl.xsodata

service { "sample.odata::revenues" as "Revenues" keys generate local "ID" aggregates always (SUM of "Amount"); }

The results of the entity set Revenues always contain the aggregated value of the column Amount. To extract the aggregated revenue amount per year, add $select=Year,Amount to your requested URI.