How can I create tables in ArcMap using SQL and/or Scripting?

ChipBrownHBG

I am interested in creating a queried table/report within ArcMap using SQL and/or a scripting language (python, perhaps). Can anyone suggest an approach or resources to get this done? Eventually, I'd like to have this as part of a model.

Here is what I can do:

I can create a table using SQL in Microsoft SQL Server Management Studio of an enterprise database.  Here is a query that works in Management Studio:

select substring(MAP_ZONING, 1, 3) as ZONING, sum((Shape_Area/43560)) as Acres, COUNT(MAP_ZONING) as Frequency
from [EnterpriseDatabase]
where MAP_ZONING is not null and ExLandUse = 'VACANT'
group by substring(MAP_ZONING, 1, 3)
order by ZONING

Note: I need only the first three characters of the values in the MAP_ZONING, thus the "substring" function.

Here is what I'd like to do:

Create a similar table, but querying a feature class that is in a file geodatabase not an enterprise geodatabase on a server.

I have tried the MakeQueryTable geoprocessing tool, but there are limitations I can't seem to work around.  One is that the "Query Builder" GUI seems to only like selecting all records after a where clause, and will not accept a query like the one above.  I have tried to figure out if I can put my query in a subquery to get it to work, but have had no success.

I think this could be done using a python script, but I am a beginner and need to find some examples and resources.

Thanks.

Chip

ChipBrownHBG

Thanks, Dan.

I have the part about creating the model by using the select by layer and copy features, what I am stuck on is how to create a queried table that contains just the information (columns, summarized records, etc) that I am looking for.  As I said above, when the I am using a table that is accessible through Microsoft SQL Server Management studio, I can create the table easily.  I am looking for any resources to be able to query the copied features that are in a local file geodatabase. I think the query is too complicated to use in the "Query Builder" GUI in ArcMap (10.5 or Pro) and think it might be a candidate for a python script.

Thoughts? Resources for building SQL queries in a python script and then adding these to a model?