aggregate functions

You are currently browsing articles tagged aggregate functions.

Let’s say you’ve got a collection of job data for a set of users that looks like this, and you want to create a nice summary of it to be displayed, with a count of how many jobs are in each category for each user, all on one line.

 │id │ user_id │ job │ status    │
 ├───┼─────────┼─────┼───────────┤
 │ 1 │ 12      │ 1   │ Completed │
 │ 2 │ 12      │ 2   │ Cancelled │
 │ 3 │ 14      │ 3   │ Ready     │
 │ 5 │ 14      │ 4   │ Completed │
 │ 6 │ 14      │ 4   │ Completed │
 │ 7 │ 14      │ 4   │ Cancelled │
 ...

Here’s  the report of summarized statuses of jobs for each user that you want.

│ user_id │ summary                           │
├─────────┼───────────────────────────────────┤
│ 12      | 1 Cancelled, 1 Completed          │
│ 14      | 1 Cancelled, 2 Completed, 1 Ready │

I’ll show you how it’s possible to provide this solely with a Postgres SELECT.

Read the rest of this entry »

Tags: , , , ,