0

I'm running some reporting queries and I want to expand the results for each record across a specific set of 4 weeks.

This is the current query:

select
  job_id,
  week,
  count(*),
  sum(count(*)) over (partition by job_id)
from candidates
group by job_id, week

Current result:

 job_id | week | count | sum 
--------+------+-------+------
   3258 |    1 |    21 |  23 
   3258 |    2 |     2 |  23 
   3259 |    1 |     1 |   4 
   3259 |    4 |     1 |   4 

But ideally, I want to expand over a specific range of 4 weeks:

Desired Result

 job_id | week | count | sum 
--------+------+-------+-----
   3258 |    1 |    21 |  23 
   3258 |    2 |     2 |  23 
   3258 |    3 |     0 |  23 # added row with 0 count
   3258 |    4 |     0 |  23 # added row with 0 count
   3259 |    1 |     1 |   4 
   3259 |    2 |     0 |   4 # added row with 0 count
   3259 |    3 |     0 |   4 # added row with 0 count
   3259 |    4 |     1 |   4 

Using a LEFT JOIN does not return the desired result as you can see in this SQL fiddle

Schema (PostgreSQL v9.6)

CREATE TABLE candidates(
   job_id integer,
   week integer,
   count1 integer,
   sum1 integer
);

INSERT INTO candidates(job_id, week, count1, sum1) VALUES (3984, 1, 13, 26);
INSERT INTO candidates(job_id, week, count1, sum1) VALUES (3984, 2, 13, 26);

INSERT INTO candidates(job_id, week, count1, sum1) VALUES (3985, 1, 42, 46);
INSERT INTO candidates(job_id, week, count1, sum1) VALUES (3985, 4, 3, 46);

Query #1

select
  c.job_id,
  weeks.week_nr as week,
  c.count1,
  c.sum1
from generate_series(1,4) as weeks(week_nr)
left join candidates c on c.week = weeks.week_nr 
order by c.job_id, week;
| job_id | week | count | sum |
| ------ | ---- | ----- | --- |
| 3984   | 1    | 1     | 2   |
| 3984   | 2    | 1     | 2   |
| 3985   | 1    | 1     | 2   |
| 3985   | 4    | 1     | 2   |
| null   | 3    | null  | null|

  • In SQLfiddle you used generate_series() and get expected result. – Arun Kumar Apr 16 at 10:43
  • Use Join instead of left join then you will get without null values. – Arun Kumar Apr 17 at 2:48
0

In postgresql we can get range values used generate_series() (or) where condition

 select job_id,week,count(*),sum(count(*)) over (partition by job_id)
 from generate_series(1,4) as weeks(week_nr)
 left join candidates c on c.week = weeks.week_nr 
 group by job_id, week order by job_id,week;

                       (or)

 select job_id,week,count(*),sum(count(*)) over (partition by job_id)
 from candidates where week>=1 and week<=4
 group by job_id, week;
  • Sadly, this does not yield the desired result. See the updated description. – ang3lkar Apr 16 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.