I found this post -- Although you can't use STDDEV as a window function, the calculations for each of these involve functions that can be done with window aggregates. So, another alternative can be to do the calculations behind the STDDEV functions instead of the functions themselvesFor STDDEV_SAMP:sqrt (( (count(x) over (partition by y,z) * sum(x**2) over (partition by y,z)) - (sum(x) over (partition by y,z))**2) / (count(x) over (partition by y,z) * count(x) over (partition by y,z) - 1) )

if you want to have your information verbatim, then how about using something like SELECT city, state,AVG(population),STDDEV_SAMP (population)from tablenamegroup by 1,2

input output putput

Hello,

do you want me to confirm that the function STDDEV_SAMP is running in combination with OLAP syntax ? I use TD V14.10 and I get an error code 3704 when I try to use it the way below:

, STDDEV_SAMP (CONSO_JOUR) OVER(

PARTITION BY POM

ORDER BY DATE_MSR

ROWS BETWEEN 28 PRECEDING AND CURRENT ROW

) AS STD_CONSO

Thanks a lot,

Of course it's working, what's your exact error message?

"is not a valid Teradata SQL token" indicates some strange character in your source code.

Dieter

Dieter,

thanks for your email.

The error seems specific to ODBC - treatment executed via SQLA - and not found using BTEQ for example.

If I consider the treatment, the function STDDEV_SAMP is applied over a derived table "T" (cf. below):`SELECT POM`

, PRM

, PS

, DATE_MSR

, TARIF

, LIB_TARIF

, CODE_INSEE

, NOM_VOIE

, PROPART

, DPT

, CONSO_JOUR AS CONSO_JOUR_TOT

, CONSO_SEM

, CONSO_2SEM AS CONSO_QUATORZ

-- CONSO ARRONDIE À 100 PRÈS, UTILE POUR FAIRE DES REGROUPEMENTS PAR CONSO PRÉCÉDENTES

, ROUND( (CONSO_SEM_PREC+50)/100)*100 AS CONSO_ARRONDIE_SEM_PREC

, ROUND( (CONSO_2SEM_PREC+50)/100)*100 AS CONSO_ARRONDIE_QUATORZ_PREC

-- STATS CONSO CLIENT

, PERCENT_RANK () OVER(

PARTITION BY POM

ORDER BY CONSO_JOUR

) AS PERCENT_CONSO

, STDDEV_SAMP (CONSO_JOUR) OVER(

PARTITION BY POM

ORDER BY DATE_MSR

ROWS BETWEEN 28 PRECEDING AND CURRENT ROW

) AS STD_CONSO

FROM (

SELECT POM

...` ) T`

If I create a working physical table and feed it with the SQL used in the derived table, the treatment is OK !

What's the exact error message?

Can you extract the actual SQL submitted from DBQL, maybe it was modified by the ODBC driver.

Try if checking "Disable Parsing" in the ODBC datasource options helps.

Dieter

Dieter, you're right => disabling the parsing allowed to execute the treatment.

I understand the parsing of the STDDEV_SAMP by the ODBC driver is not correct, right ?

Thanks again for your help !

I don't think that the ODBC driver changed the STDDEV_SAMP, but maybe a calculation of a column used within, that's why I was asking for the exact error message and checking DBQL :-)

Dieter

I want to use the STDDEV_SAMP() with the OVER (Partition by) clause.Teradata's standard deviation functions do not allow this. I want to calculate the standard deviation for each subset of data.Any suggestions or workarounds would be greatly appreciated.Thanks.Kind of what I am looking to do: SELECT city, state ,AVG(population) OVER (PARTITION BY city,state) ,STDDEV_SAMP (population) OVER (PARTITION BY city,state)from tablename