The feature layers published on Arcgis Server could have the following types of source.
Each source type gives you different query capabilities.
1) ESRI file geodatabase as source
Arcgis REST API will supports Advanced Queries, supports Statistics, supports Count Distinct, supports Distinct
This gives you most advanced query capability. (most recommended)
Data geometry use Esri ST_Geometry as data type.
Try this sample below.
Click here open full screen in a new tab
2) Oracle or Microsoft SQL server as source
Arcgis REST API will supports Advanced Queries, supports Statistics, supports Count Distinct, supports Distinct
Warning:
However, outStatistics ( in Arcgis REST API) , are not supported. Statistics query ( in Arcgis REST API) is only working on feature classes with ST_GEOMETRY storage. click here to see why? click here for more outStatistics return 'unable to complete operation'
https://community.esri.com/t5/arcgis-enterprise-questions/outstatistics-return-unable-to-complete-operation/td-p/456011
Oracle use Oracle's SDO_GEOMETRY are not work with statistic query( in Arcgis REST API) . Only ST_GEOMETRY works. Microsoft SQL server use Microsoft's Geometry type, they are not work with statistic query( in Arcgis REST API) as well.
Oracle or Microsoft SQL server as source gives you a limited advanced query capability without statistic query ( in Arcgis REST API) .
In Oracle or Microsoft SQL server as source, you can use 'disticnt' ( in Arcgis REST API) , but not 'group by' ( in Arcgis REST API) . Group-by ( in Arcgis REST API) is part of statistic query ( in Arcgis REST API) , distint ( in Arcgis REST API) is not part of statistic query ( in Arcgis REST API) .
My multidimensional classified apps works with both source ( file geodatabase. or oracle-microsoft-sql server ), but behave differently:
Oracle or Microsoft SQL server as source, multidimensional classified apps can only use 'distinct' ( in Arcgis REST API) , user have to click each field value node to get total count of that, for example, you click any field value 'xxxxxxx', it becomes xxxxxxx {~ total count ~}
File geodatabase as source, multidimensional classified apps can use both 'distinct' and 'group by' ( in Arcgis REST API) . I set it to use 'group by' ( in Arcgis REST API) , because it show all field value filtered total count at the beginning all at once. For example, it show all field value as xxxxxxx {~ total count ~} all the time. User don't have to click each field value to find out how many item have that value. Of couse it need more calculation power to bring this convenience.
Try this sample below.
Click here open full screen in a new tab
3) shape file as source
Arcgis REST API will NOT supports Advanced Queries, NOT supports Statistics, NOT supports Count Distinct, NOT supports Distinct
'distinct' ( in Arcgis REST API) and 'group by' ( in Arcgis REST API) are NOT supports, statistic query ( in Arcgis REST API) are NOT supports
My multidimensional classified apps still works with shape file as source, but all calculation will be performed in user browser (client side). It is not like file geodatabase and Oracle Microsoft SQL server, most calculation performed on server side.
Try this sample below.
Click here open full screen in a new tab
More on Los Angeles Arcgis server
Click here open full screen in a new tab