get sum of count of count postgres

Question

I have two tables jobs and users. Users has a one-to-many relationship with jobs.

I want to segment users into groups of jobs_done. In other words, how many users did 1 job, 2 jobs, 3 jobs, etc The below query does that. However, I would like to lump together all users that have done 3 or more jobs into one group.

Here is the query I currently have

select
  jobs_done,
   count(1) as number_of_users
  from ( select 
     u.id,
      count(*) as jobs_done
   from jobs j
  JOIN users u on j.user_id = u.id 
  group by  u.id ) a
group by jobs_done

Current Output:

times_used  number_of_users  
1           255  
2           100  
3           30  
4           10  
5           9

Desired Output:

times_used  number_of_users  
1           255  
2           100  
3+          49

You should have posted your schema and it is not entirely clear what you want as output. So showing us a sample output would help. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 11, 2015 at 0:24
Can the same jobs_done be recorded in jobs for the same user_id more than once? — Glenn
– Glenn, Commented Aug 11, 2015 at 0:26

jpw · Accepted Answer · 2015-08-11 01:05:28Z

2

You can use a case expression to group values 3+ into one large group. This should work:

select
    case 
       when jobs_done >= 3 then '3+' 
       else cast(jobs_done as varchar(5)) 
    end as jobs_done,
    count(1) as number_of_users
from ( 
    select 
       u.id,
       count(*) as jobs_done
    from jobs j
    join users u on j.user_id = u.id 
    group by  u.id 
) a
group by case when jobs_done >= 3 then '3+' 
             else cast(jobs_done as varchar(5)) 
         end;

answered Aug 11, 2015 at 1:05

jpw

45k6 gold badges71 silver badges90 bronze badges

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

Comments

Hans-Jürgen Schönig · Accepted Answer · 2015-08-11 09:52:15Z

0

you can group by basically everything. this is a simplistic example:

test=# SELECT CASE WHEN x < 4 THEN x::text ELSE '4+' END AS y, 
              count(*) 
       FROM generate_series(1, 10) AS x 
       GROUP BY y 
       ORDER BY 1;
 y  | count 
----+-------
 1  |     1
 2  |     1
 3  |     1
 4+ |     7
(4 rows)

answered Aug 11, 2015 at 9:52

Hans-Jürgen Schönig

7616 silver badges4 bronze badges

Collectives™ on Stack Overflow

get sum of count of count postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related