count and group by postgresql-CodePudding

everyone! My table:

  project_id | task_id | foo | bar | job_id |
    ===========================================
      1             12      x    y      13
      1             12      x    z      14
      1             12      a    b      15
      1             1210    x    y      1211
      1             1210    z    v      1212 
      1             1220    aa   bb     1221

I trying quiery where group by by task_id and count job_id per task_id So I expect something like that:

project_id | task_id | foo | bar | job_id | countJobsperTask |
==============================================================
    1             12      x    y      13          3
    1             12      x    z      14          3        
    1             12      a    b      15          3
    2             1210    x    y      1211        2
    2             1210    z    v      1212        2
    3             1220    aa   bb     1221        1

My not successfully query :

select project_id, task_id, foo, bar, job_id, count(job_id) as "countJobsperTask" 
from project 
group by project_id, task_id, job_id

CodePudding user response：

I don't understand why project_id is different between your sample data and expected data, but this query maybe helps you:

demo

select 
  project_id, 
  task_id, 
  foo, 
  bar, 
  job_id, 
  count(job_id) over (partition by project_id, task_id)
from project