CODEWITHSUNDEEP
sqlpostgresql
DRD
DRD

Reputation: 5813

Field value counts with aggregate conditions

Suppose I have the following applicant data for jobs in a company:

id  position    salary
——————————————————————
0   senior      20000
1   senior      15000
2   associate   10000

The budget is 40000 and the preference is to hire senior managers. What PostgreSQL constructs do I use to get the following result as far as the number of hires are concerned.

seniors  associates
———————————————————
   2         0

Any directions would be appreciated.

Here is a starting sqlfiddle: http://sqlfiddle.com/#!17/2cef4/1

Upvotes: 1

Views: 44

Answers (1)

DRD
DRD

Reputation: 5813

Using PostgreSQL filters and window functions, I was able to come up with a query that produced the result.

select 
  count(*) filter(where s.position = 'senior') as seniors,
  count(*) filter(where s.position = 'associate') as associates
from (
  select 
    position, 
    sum(salary) over(order by position desc rows between unbounded preceding and current row) as salary 
  from 
    candidates
) as s
where s.salary <= 40000;

Example: http://sqlfiddle.com/#!17/2cef4/10

Upvotes: 1

Related Questions