Hi, Perhaps a CTE would help?
WITH NormCTE AS ( SELECT delta - avg(delta))/stddev(delta) AS deltaNorm , (echo - avg(echo))/stddev(echo) AS echoNorm , (foxtrot - avg(foxtrot))/stddev(foxtrot) AS foxtrotNorm FROM t_subs ) SELECT deltaNorm + echoNorm + foxtrotNorm AS normSum FROM NormCTE ORDER BY normSum DESC HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl 2014-08-03 13:20 GMT+02:00 Tim Smith <gb10hkzo-postg...@yahoo.co.uk>: > Hi, > > I'm on Postgres 9.3.5, however I think my knowledge of Postgres is not > deep enough to help me with this challenge, so here I am reaching out to > the community ! > > Let's say I have a table as follows : > > create table t_subs (alpha text,bravo text,charlie numeric,delta > numeric,echo numeric,foxtrot numeric); > > And let's say I have a view that does some basic filtering on that table > > create view v_subs as select alpha,delta,echo,foxtrot from t_subs where > charlie>=5 and bravo not in ('this','that'); > > What I need to do is order the output of the view based on normalised > output of delta,echo and foxtrot. > > So, what I need to do is : > > 1/ Calculate normalised values for each column and row.... > > deltaNorm = (delta - avg(delta))/stddev(delta) > echoNorm = (echo - avg(echo))/stddev(echo) > > foxtrotNorm = (foxtrot - avg(foxtrot))/stddev(foxtrot) > normSum = deltaNorm + echoNorm + foxtrotNorm > > 2/ order desc on normSum > > The problem is I cannot seem to find a way to do this in one query. > > Thanks in advance for your help ! > > Tim > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >