Percent of Total in Histogram Query

Поиск
Список
Период
Сортировка
От Paul Jungwirth
Тема Percent of Total in Histogram Query
Дата
Msg-id CA+6hpakmz34Uzta4=rBJdtmOUWk9gKQR6b2t-JMke8ZvhZddaQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Percent of Total in Histogram Query  ("David Johnston" <polobo@yahoo.com>)
Список pgsql-general
Hello,

I'd like to write a histogram-like query that shows these columns:
  - x-value from 0 to k.
  - number of rows with that x-value.
  - number of rows seen so far (i.e. with the current x-value or less).
  - % of total rows seen so far.

The following query works for the first three columns, but the last column gives me an error:

        SELECT c,
                COUNT(*) AS items_count,
                SUM(COUNT(*)) OVER (ORDER BY c) AS total_items_count,
                SUM(COUNT(*)) OVER (ORDER BY c) / SUM(COUNT(*)) AS total_items_perc
        FROM    (SELECT p.id, COUNT(*) c
                 FROM   parent p, child ch
                 WHERE  p.id = ch.parent_id
                 GROUP BY p.id
                ) x
        GROUP BY x.c
        ORDER BY x.c

Including that fourth SELECT column gives me this error:

    ERROR:  aggregate function calls cannot be nested

Is there any way to get % of total in a query like this?

Thanks,
Paul

--
_________________________________
Pulchritudo splendor veritatis.

В списке pgsql-general по дате отправления:

Предыдущее
От: Vlad
Дата:
Сообщение: Re: High SYS CPU - need advise
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: High SYS CPU - need advise