Re: windown function count Unexpected results

hello winston,

by your data,

select dep_id, ename, salary, count(*) over (partition by dep_id) from
employee where salary<3000 order by dep_id ;
dep_id | ename | salary | count
--------+-------------------+--------+-------
30 | Shelli Baida | 2900 | 4
30 | Sigal Tobias | 2800 | 4
30 | Guy Himuro | 2600 | 4
30 | Karen Colmenares | 2500 | 4
50 | Michael Rogers | 2900 | 20
50 | Timothy Gates | 2900 | 20
50 | Vance Jones | 2800 | 20
50 | Mozhe Atkinson | 2800 | 20
50 | Girard Geoni | 2800 | 20
50 | John Seo | 2700 | 20
50 | Irene Mikkilineni | 2700 | 20
50 | Douglas Grant | 2600 | 20
50 | Randall Matos | 2600 | 20
50 | Donald OConnell | 2600 | 20
50 | Joshua Patel | 2500 | 20
50 | Peter Vargas | 2500 | 20
50 | Martha Sullivan | 2500 | 20
50 | James Marlow | 2500 | 20
50 | Randall Perkins | 2500 | 20
50 | James Landry | 2400 | 20
50 | Ki Gee | 2400 | 20
50 | Hazel Philtanker | 2200 | 20
50 | Steven Markle | 2200 | 20
50 | TJ Olson | 2100 | 20

2017-05-05 11:31 GMT+08:00 winston cheung <winston_cheung(at)163(dot)com>:

> hello
>
> When I used the count in the window function, I found that the results
> were not true. In the table I use department_id as a partition condition, I
> think when department_id = 30, the calculation of salary <3000 should be 4,
> when department_id = 50,the calculation of salary < 3000 should be 20, why
> the value of each line are different?
>
> Thanks a lot!
>
> postgres(at)160(dot)40:5410/testdb=# select department_id as dep_id,
> concat(first_name, ' ', last_name) as employee_name, salary, count(*) over
> w from employees where salary<3000 window w as (partition by department_id
> order by salary desc);
> dep_id | employee_name | salary | count
> --------+-------------------+---------+-------
> 30 | Shelli Baida | 2900.00 | 1
> 30 | Sigal Tobias | 2800.00 | 2
> 30 | Guy Himuro | 2600.00 | 3
> 30 | Karen Colmenares | 2500.00 | 4
> 50 | Michael Rogers | 2900.00 | 2
> 50 | Timothy Gates | 2900.00 | 2
> 50 | Vance Jones | 2800.00 | 5
> 50 | Mozhe Atkinson | 2800.00 | 5
> 50 | Girard Geoni | 2800.00 | 5
> 50 | John Seo | 2700.00 | 7
> 50 | Irene Mikkilineni | 2700.00 | 7
> 50 | Douglas Grant | 2600.00 | 10
> 50 | Randall Matos | 2600.00 | 10
> 50 | Donald OConnell | 2600.00 | 10
> 50 | Joshua Patel | 2500.00 | 15
> 50 | Peter Vargas | 2500.00 | 15
> 50 | Martha Sullivan | 2500.00 | 15
> 50 | James Marlow | 2500.00 | 15
> 50 | Randall Perkins | 2500.00 | 15
> 50 | James Landry | 2400.00 | 17
> 50 | Ki Gee | 2400.00 | 17
> 50 | Hazel Philtanker | 2200.00 | 19
> 50 | Steven Markle | 2200.00 | 19
> 50 | TJ Olson | 2100.00 | 20
> (24 rows)
>
>
> winston , regards
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>