sql - PostgreSQL: Window functions using column alias -

i have following table :

                                    table "public.activity"     column   |            type             |           modifiers                        ------------+-----------------------------+-------------------------------------------------------  id         | integer                     | not null default nextval('activity_id_seq'::regclass)  scheduleid | integer                     |   name       | text                        |   duedate    | timestamp without time zone |  indexes:     "activity_pkey" primary key, btree (id) 

with following data:

 id | scheduleid |   name   |          duedate            ----+------------+----------+----------------------------   1 |          1 | act1     | 2015-09-21 13:34:53.738449   2 |          1 | act1     | 2015-09-20 13:35:02.770369   3 |          1 | act1     | 2015-09-19 13:35:07.650204   4 |          1 | act1     | 2015-09-18 13:35:11.930225   5 |          1 | act1.0.0 | 2015-09-17 13:35:48.033791   6 |          1 | act1.0.0 | 2015-09-16 13:35:51.55382   7 |          2 | act2.0.0 | 2015-09-21 13:36:56.42534   8 |          2 | act2.0.0 | 2015-09-28 13:37:21.065071   9 |          2 | act2.0.0 | 2015-10-05 13:37:26.753227  10 |          2 | act2.0.0 | 2015-10-12 13:37:30.656846  11 |          2 | act2.0.0 | 2015-10-19 13:37:34.54473  12 |          2 | act2.0.0 | 2015-10-26 13:37:38.192843 (12 rows) 

for each scheduleid, have activities created.

i need display latest unique activity each schedule along count of activities clubbed under it.

following query using postgres window functions job.

with top_activities (     select distinct on (scheduleid, name)     id, scheduleid, name, duedate,     count(*) over(partition scheduleid, name) clubbedcount     activity order scheduleid, name, duedate desc ) select * top_activities; 

the result follows:

id | scheduleid |   name   |          duedate           | clubbedcount  ----+------------+----------+----------------------------+--------------   1 |          1 | act1     | 2015-09-21 13:34:53.738449 |            4   5 |          1 | act1.0.0 | 2015-09-17 13:35:48.033791 |            2  12 |          2 | act2.0.0 | 2015-10-26 13:37:38.192843 |            6 

so far :p

now small twist that, need group activities rangetag too

eg: todays date being 21-sep-2015, activities duedate <= now() --> club under today tag activities duedate <= now() + 7 days --> club under week tag activities duedate <= now() + 1 month --> club under month tag else --> club under future tag  

thus need 1. top shelf activity each partition defined rangetag, scheduleid , name 2. count of activities, collated each partition top activity.

slightly modifying query to:

with top_activities (      select distinct on (range, scheduleid, name)      id, scheduleid, name, duedate,       case when duedate < now() 'today'           when duedate < now() + interval '7 days' 'this week'           when duedate < now() + interval '1 month' 'this month'           else 'future'       end range,       count(*) over(partition scheduleid, name)        activity order range, scheduleid, name,duedate desc ) select * top_activities order scheduleid; 

gives me near desired result, except count :p

 id | scheduleid |   name   |          duedate           |   range    | count  ----+------------+----------+----------------------------+------------+-------   1 |          1 | act1     | 2015-09-21 13:34:53.738449 | today      |     4   5 |          1 | act1.0.0 | 2015-09-17 13:35:48.033791 | today      |     2  12 |          2 | act2.0.0 | 2015-10-26 13:37:38.192843 | future     |     6  11 |          2 | act2.0.0 | 2015-10-19 13:37:34.54473  | month |     6   8 |          2 | act2.0.0 | 2015-09-28 13:37:21.065071 | week  |     6   7 |          2 | act2.0.0 | 2015-09-21 13:36:56.42534  | today      |     6 

i need count partitioned "range" too.

but, replacing

count(*) over(partition scheduleid, name) 

with

count(*) over(partition range, scheduleid, name)  

doesn't work.

error is

error: column "range" not exist
line 9: count(*) over(partition range,scheduleid, name)

move count() (and distinct on) new query:

with top_activities (     select          id, scheduleid, name, duedate,         case when duedate < now() 'today'             when duedate < now() + interval '7 days' 'this week'             when duedate < now() + interval '1 month' 'this month'             else 'future'           end range     activity order range, scheduleid, name,duedate desc     ), top_activities_with_count (       select distinct on (range, scheduleid, name)         *, count(*) over(partition range, scheduleid, name)     top_activities     ) select * top_activities_with_count order scheduleid;