Question 250003 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: How to sum distinct in a single query?

I’ve been working on a small summary table that reads from the following:

create table page_event (
  user_id uuid,
  viewed_at timestamp,
  type smallint,
  product_id integer,
  ad_id integer
);

And my query is quite simple:

select
  type,
  product_id,
  count(*) as counts,
  count(distinct user_id) as uniques
from
  page_event
where
  created_at >= '2023-09-01 14:17:00' and created_at < '2023-09-01 14:17:59'
group by
  type,
  product_id

However, this result gives me the event types separated. type can be 0, 1, 2, 3, in fact it is an application-side enum. Currently, I’m aggregating that in application code and inserting into a summary table. It is pretty fast and that’s enough for the use case. However, I was wondering if it would be possible to do this in a single-query, so I could get the results of that select directly into the INSERT of my summary table.

I tried the following:

select
  product_id,
  sum(case when type = 0 then 1 else 0 end) as search,
  sum(case when type = 1 then 1 else 0 end) as sidebar,
  sum(case when type = 2 then 1 else 0 end) as pdp
from
  page_event
where
  created_at >= '2023-09-01 14:17:00' and created_at < '2023-09-01 14:17:59'
group by
  product_id

But I can’t sum() with distinct (user_id). This is how the summary table looks like:

create table event_summary (
  created_at timestamp,
  product_id integer,
  ad_id integer,
  search integer,
  unique_search integer,
  sidebar integer,
  unique_sidebar integer,
  pdp integer,
  unique_pdp integer
);
create index on event_summary using BRIN (created_at);
Tags:

2

Answers


  1. 0

    If you need to count distinct users for each type then use count() combined with distinct :

        select
      product_id,
      count(distinct case when type = 0 then user_id end) as search,
      count(distinct case when type = 1 then user_id end) as sidebar,
      count(distinct case when type = 2 then user_id end) as pdp
    from
      page_event
    where
      created_at >= '2023-09-01 14:17:00' and created_at < '2023-09-01 14:17:59'
    group by
      product_id
    Login or Signup to reply.
  2. 0

    You may always use a FILTER clause for this purpose (not only for count, but also for count distinct

    Example

    select 
  product_id,
  count(distinct user_id) filter (where type = 1) cnt_dist_1,
  count(*)  filter (where type = 1) cnt_1
from t
group by 1
order by 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search