standard deviation function doesnt seem to be behaving like an aggregate

Question

Post reply

standard deviation function doesnt seem to be behaving like an aggregate

stan

Ten Centuries

Points: 1032
More actions
January 11, 2025 at 7:26 pm

#4513910

hi i have 6 statistical populations in my overall data set. They run across 21 data points. No matter what i try with the standard dev function, i get back all 21 data points, each with a repeated std dev for the population that data point is in. from the looks of the function it seems it should be able to return 6 standard deviations, one for each population but i dont see how.
POP is the field name that contains the unique statistical population names.
ive tried ...
select pop, stdev(mymeasure) over (partition by pop) stdevvalue from mytablename
...and every combo of allowable syntax including 1) without pop in the select, 2) without a partition by, 3) with an order by etc etc. do i have to include the select in a subselect to make this work, ie select distinct pop, stddevvale from (what you see above) x? i used this link which i think is inaccurate in its claim that an order by is required https://learn.microsoft.com/en-us/sql/t-sql/functions/stdev-transact-sql?view=sql-server-ver16 .

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63523 More actions · Answer 1

Why are you using a windowing function then? Why not just use a regular aggregate version of the function?

stan Ten Centuries Points: 1032 More actions · Answer 2

that is the question. what are you suggesting? how is what you are suggesting done? are you suggesting 6 selects? the number of statistical populations varies based on the filter parameter values chosen in an ssrs report.

This reply was modified 1 week ago by stan.
This reply was modified 1 week ago by stan.

pietlinden SSC Guru Points: 63523 More actions · Answer 3

If you want the standard deviation for each record, then just use the non-windowing version of the function.

Do you already have a data source and dataset defined for your report, or are you writing SQL for it? You can use the standard deviation function that's available in SSRS too.

stan Ten Centuries Points: 1032 More actions · Answer 4

i'd rather have it for each statistical population. I see you are saying stdev isnt like sum where "group by" limits what you see. I think i understand. since i want the std dev from t-sql i think you would recommend let it show on each record in a sub select. and have the "outer" select pick off distinct population name and std dev from the sub select . i guess thats ok. i'll mark your initial post as an answer if i dont hear anything to the contrary.