How to aggregate data in SQL within nested groups

Question

I am using PostgreSQL and have a table like this: See the sql fiddle here: http://sqlfiddle.com/#!1/253b4/1

CREATE TABLE usage_sample
(
  id serial primary key, 
  category text NOT NULL,
  name text NOT NULL,
  sample_time timestamp with time zone NOT NULL
);

I need to write a query that returns a count for the number of samples within each minute and have this set of counts for each combination of category and name. So the output we be something like:

cat1, act1, minute1, count
cat1, act1, minute2, count
cat1, act2, minute1, count
cat1, act2, minute3, count
cat2, act1, minute2, count
...

I know how to get a count for each minute using group by and how to get a count for each combo of category and action, but I can't figure out how to combine these so I can nest the groupings and counts.

The sql fiddle http://sqlfiddle.com/#!1/253b4/1 shows an example data set and the queries I have tried to start with. Can anyone give me advice on how to get the result I need?

Gordon Linoff · Accepted Answer · 2013-03-21 14:09:45Z

4

Is this what you want?

select category, name, date_trunc('minute', sample_time) as minute, count(*) 
from usage_sample 
group by category, name, date_trunc('minute', sample_time)
order by category, name, minute

answered Mar 21, 2013 at 14:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Allen Over a year ago

That works. Not quite sure why I didn't see that before. Thanks. I have an updated fiddle here: sqlfiddle.com/#!1/785a0/2

Collectives™ on Stack Overflow

How to aggregate data in SQL within nested groups

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