Use aggregate function without group by caluse?

Database

Use aggregate function without group by caluse?

Dear Folks,

I want to rank the whole table to select 50th percentile of data. My sample code is as following:

select EQP_INIT, EQP_NUMB, RNK_KIP, AVG(RNK_KIP) AS MED_KIP

FROM TABLE

WHERE RNK_KIP<=MED_KIP

NOTE: RNK_KIP is the kip that ranked across the whole table.

Unfortunately, the result indicated that '3504: selected non-aggregate values must be part of the associated group.

It seems to me I cannot aggregate a column without group by clause. If so, what should I do?

Thanks a lot.

Harry

2 REPLIES
Junior Contributor

Re: Use aggregate function without group by caluse?

Hi Harry,

what's your Teradata release?

"50th percentile" is the median, TD14.10 added PERCENTILE_CONT, PERCENTILE_DISC and MEDIAN as new OLAP function (in fact aggregate functions).

If those functions don't exists on your system (or you want the full row instead of an aggregate): 

Missing Functions: PERCENTILE_DISC, PERCENTILE_CONT & MEDIAN

Re: Use aggregate function without group by caluse?

Dear Dnoeth,

Thanks very much for your message. My TD version is 13.00, so these functions are unavailable to me. The website you provided is extremely useful. I like it very much.

Thanks again for your time.

Harry