I thought perhaps there would be a CTE solution, however it appears aggregate functions don't work at all on CTEs? This query works:
with T(n) as (select 1 union all select n+1 from T where n < 10) select n from T but this throws an error: with T(n) as (select 1 union all select n+1 from T where n < 10) select avg(n) from T On Sunday, July 13, 2014 9:01:31 AM UTC-7, Brian Craft wrote: > > I suspect this is more of a sql question, but is there a way to get a > function of an aggregate and a row, like removing the mean from the value > of a column? > > This throws an error, saying "i" must be in a "group by": > > select i - avg(i) from table (i int = (1,2,3,4)) > > "group by" returns the average per-row, so using it gives me zero for > every row. > > select i - avg(i) from table (i int = (1,2,3,4)) group by i > > I can do (what I think is) a cross join with the aggregate, which gives > the desired result, but requires putting the subselect (table function in > this example) in the query twice: > > select i - avgi from table (i int = (1,2,3,4)) join (select avg(i) as avgi > from table(i int = (1,2,3,4))) > > This is really ugly, especially for a complex subselect. Is there any > performance hit for a subselect appearing twice? > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
