I recently discovered that you cannot use standard aggregate or grouping SQL keywords (i.e. COUNT, GROUP BY, SUM) in data query functions within both ArcMap (as of 10.6) and ArcGIS Pro (I am running 2.1), at least without using a subquery - which is not always a straightforward use case.
This is not as big a deal for functions like Select by Attribute, but is a serious limitation on tools like Make Query Layer (or Table) in terms of their usefulness for data analysis and extraction workflows.
For example, I recently had a scenario where I had a polygon feature class of tax parcels and three related tables containing tax assessment information for parcels in Missoula County: Property, Commercial, and Residential. Each parcel has a geocode (unique ID). The Property table contains general tax assessment information for each parcel and tax year. The Property ID uniquely identifies a tax parcel in a given tax year (one-to-one relationship with parcels). The Residential and Commercial tables contain assessment information on residential or commercial improvements (structures) for each parcel and tax year. There may be zero or multiple records for each tax parcel/property ID (one-to-many relationship).
Here is an example of what these tables look like, with PropertyID 327491 highlighted.
If I want to count how many residential, commercial, or total structures are associated with each parcel for the whole county, currently I have a process in Modelbuilder using a combination of ArcToolbox tools (i.e. Summary Statistics and Join Field) to:
1) count (SUM) the number of PropertyID instances in the Residential and Commercial Tables by tax year.
2) Join it to the Property table.
3) Count (SUM) the number of propertyIDs, commercial structures, and residential structures by geocode and tax year (GROUP BY).
4) Join the aggregate structure and property ID counts to the tax parcel feature class for visualization and analysis.
While this model is helpful, it has to process over 15,000 records in each table and takes about 6-8 hours to run, so making adjustments to the model or re-running it is time-intensive.
When I discovered the Make Query Table and Make Query Layer tools, I thought, what a great replacement! I can write an SQL query that will prepare that table for me and then I can either create a join or write a Python script to extract data from it. An example SQL Query might look like this:
SELECT Property.GeocodeSearch,Property.PropertyID,Property.TaxYear, COUNT(Res.PropertyID) AS ResCount FROM Res, COUNT(Com.PropertyID) AS ComCount FROM Com
LEFT JOIN Property ON Property.PropertyID = Res.PropertyID
LEFT JOIN Property ON Property.PropertyID = Com.PropertyID
GROUP BY Property.Geocode;
WHERE Property.TaxYear = 2017;
Unfortunately, this is not possible with SQL on the client side (in ArcGIS Desktop - ArcMap or ArcGIS Pro) unless I converted the above SQL statement into an appropriate series of subqueries, which is beyond my skills. For those GIS users who don't have database privileges to create a view on the database side, this can result in a loss of query/extraction function and having to develop slow or clunky workarounds.
Please consider expanding support for predicate, aggregate, and grouping functions in ArcMap and ArcGIS Pro to enable these types of data workflows.