Aggregating over multiple groups in Postgres

Question

I have a table with the following data:

+------------+-------------+---------------+
| shop_id    |  visit_date | visit_reason  |
+------------+-------------+---------------+
|      A     | 2010-06-14  | shopping      |
|      A     | 2010-06-15  | browsing      |
|      B     | 2010-06-16  | shopping      |
|      B     | 2010-06-14  | stealing      |
+------------+-------------+---------------|

I need to build up an aggregate table that is grouped by shop, year, month, activity as well as total values for year and month. For example, if Shop A has 10 sales a month and 2 thefts a month and no other types of visit then the return would look like:

shop_id, year, month, reason, reason_count, month_count, year_count

  A,     2010, 06,    shopping, 10,         12,          144
  A,     2010, 06,    stealing, 2,          12,          144

Where month_count is the total number of visits, of any type, to the shop for 2010-06. Year-count is the same except for 2010.

I can get everything except the month and year counts with:

SELECT 
  shop_id, 
  extract(year from visit_date) as year,
  extract(month from visit_date) as month,
  visit_reason as reason,
  count(visit_reason) as reason_count,
FROM shop_visits
GROUP BY shop_id, year, month

Should I be using some kind of CTE to double group by?

Gordon Linoff · Accepted Answer · 2019-07-18 12:35:10Z

1

You can use window functions to add up the counts. The following is phrased using date_trunc(), which I find more convenient for aggregating by month:

select shop_id, date_trunc('month', visit_date) as yyyymm, reason,
       count(*) as month_count,
       sum(count(*)) over (partition by shop_id, date_trunc('year', min(visit_date))) as year_count
from t
group by shop_id, date_trunc('month', visit_date), reason;

edited Jul 18, 2019 at 12:35

answered Jul 18, 2019 at 11:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

RedM Over a year ago

Great! Works perfectly except for a ')' missing on year_count. Also renamed "month_count" to "reason_count" but the partition idea works 100%

RedM Over a year ago

I'm seeing a slight problem. I tested on a subset of my data "where shop = 'A'" and it worked fine. When I test on the full table I get "year_count" as the count for all visits for all shops for the year. Need to edit year_count to be sum(count(*)) over (partition by date_trunc('year', min(visit_date)), shop_id)

Gordon Linoff Over a year ago

@Redm . . . shop_id should be int he partition by.

Collectives™ on Stack Overflow

Aggregating over multiple groups in Postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related