Hi Richard,
I too get the same situation. Here i uses "Inner Join", but it is not enabled as a function in add query layer in arcgis pro.
query:
select w.*, r.Total_plots from FGDABEDITZ40.SECTORTPSS AS w
Inner JOIN
(SELECT PLOTINTERNALID,SECTORTPSSNUMBER,COUNT(SECTORTPSSNUMBER) AS Total_plots FROM FGDABEDITZ40.PLOT GROUP BY SECTORTPSSNUMBER) AS r
ON w.SECTORTPSSNUMBER=r.SECTORTPSSNUMBER