FedSQL grouping error

[dcortell]

Hi people

The below is causing this error: 

ERROR: Column "AAAA.PRECISIONS" must be GROUPed or used in an aggregate function
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.

No idea what is generating the error in the final aggreagation, as all the sub-queries work fine (tested)

proc fedsql sessref=casauto;

create table casuser.test as


select

mean(precisions) as mean_precision,
median(precisions) as median_precision

from

(

select 
aaa.person_id,
count(distinct(aaa.gms_deliverable_id))/5 as precisions


from 

(
select
a.person_id,
b.gms_deliverable_id

from
user_recc_open a

left join 
recc_test_open b

on a.person_id=b.person_id 
and inputn(a.gms_deliverable_id,'best.')=b.gms_deliverable_id
) aaa

group by aaa.person_id

) aaaa
;

quit;

[dcortell]

I add that If I just remove the median function from the code, the error disappear and the code works , which is weird cause the median function is listed as available function for fedsql https://go.documentation.sas.com/doc/en/pgmsascdc/v_038/fedsqlref/n12fyogjg75fznn1dk3worxdalfs.htm

median(precisions) as median_precision

[yabwon]

Median() is "row function" i.e. it works on multiple variables, e.g. median(a.x,a.y,a.z) and returns medin of x, y, and z.

Mean() is "row function" too. 

Your code works wit Mean() because Mean() is alias for AVG() and AVG() as "column function".

Two different pages in doc:

AVG: https://go.documentation.sas.com/doc/en/pgmsascdc/v_038/fedsqlref/p0914cxk53dlurn1lmnfevdiekm2.htm

MEAN: https://go.documentation.sas.com/doc/en/pgmsascdc/v_038/fedsqlref/p1gtv0q63ascpcn139jw8tpnvbjw.htm

Bart

[Tom · Accepted solution]

There is no aggregate MEDIAN() function in FEDSQL.

Since the MEDIAN of a single value is just that value using it that way in your query makes no sense. If you replace it with its equivalent 

precisions as median_precision

It is clearer why FEDSQL is complaining.  Unlke PROC SQL in FEDSQL SAS will not automatically remerge aggregate results back onto all of the individual rows.

If you want to calculate the median over the whole dataset then use PROC MEANS or PROC UNIVARIATE.