Re: query on query

On Fri, Jul 05, 2013 at 08:35:22AM +0200, Luca Ferrari wrote:
> On Fri, Jul 5, 2013 at 5:09 AM, Jayadevan M
> <jayadevan.maymala@ibsplc.com> wrote:
>
> >
> > So each student may get counted many times, someone with 99 will be counted
> > 10 times. Possible to do this with a fat query? The table will have many
> > thousands of records.
> >
>
>
> Not sure I got the point, but I guess this is a good candidate for a CTE:
>
> WITH RECURSIVE t(n) AS (
>     VALUES (10)
>   UNION ALL
>     SELECT n+10 FROM t WHERE n < 50
> )
> select count(*), t.n from m, t where mark > t.n group by t.n;

This might get expensive with many rows.

On the other hand, you can do it like this:

create table grades (username text, grade int4);
insert into grades select 'x', int(rand() * 50) from generate_series(1,100);

with a as
  (select (grade/10)*10 as mark,
                     count(*)
   from grades
   group by mark)
select mark,
       sum(count) over (
                        order by mark)
from a
order by mark;

Whis should be faster.

Best regards,

depesz