count and group by postgresql

Question

This is 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 to write a query 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 very successful 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

Based on your test data, each of your groups contains exactly one row, as specified by your GROUP BY terms. If you want to select the original rows (ungrouped), but calculate an aggregate OVER some similar rows, you can use window functions, as shown by @Pooya's answer. There are also several ways to use GROUP BY in a separate expression and then refer to that in your select list. There is a question about how you want to count job_id (distinct or not). That would need to be specified. — Jon Armstrong
– Jon Armstrong, Commented Nov 14, 2021 at 13:41

Pooya · Accepted Answer · 2021-11-14 13:24:49Z

1

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

answered Nov 14, 2021 at 13:24

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TeoK Over a year ago

you understood correctly there are still different projects

Collectives™ on Stack Overflow

count and group by postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related