Or... Do you mean to use windowing functions? http://www.postgresql.org/docs/9.3/static/tutorial-window.html
Or both of course... Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl 2014-08-04 19:43 GMT+02:00 Serge Fonville <serge.fonvi...@gmail.com>: > 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 >> > >
