5

I have a table events like this:

create table events
(
    correlation_id char(26) not null,
    user_id        bigint,
    task_id        bigint not null,
    location_id    bigint,
    type           bigint not null,
    created_at     timestamp(6) with time zone not null,
    constraint events_correlation_id_created_at_user_id_unique
        unique (correlation_id, created_at, user_id)
);
CREATE TABLE

The table holds records of tasks being performed, that look like this:

insert into events (correlation_id, user_id, task_id, location_id, type, created_at)
values  ('01CN4HP4AN0000000000000001', 4, 58, 30, 0, '2018-08-17 18:17:15.348629+00'),
        ('01CN4HP4AN0000000000000001', 4, 58, 30, 1, '2018-08-17 18:17:22.852299+00'),
        ('01CN4HP4AN0000000000000001', 4, 58, 30, 99, '2018-08-17 18:17:25.535593+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 0, '2018-08-17 18:28:00.104093+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 98, '2018-08-17 18:28:49.04584+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 99, '2018-08-17 18:29:09.01684+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 0, '2018-08-17 18:29:12.963264+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 3, '2018-08-17 18:29:47.83452+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 98, '2018-08-17 18:31:01.86342+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 99, '2018-08-17 18:32:09.272632+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 0, '2018-08-17 18:50:09.725668+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 3, '2018-08-17 18:50:11.842+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 99, '2018-08-17 18:51:42.240895+00'),
        ('01CNC4G1Y40000000000000008', 139, 99, 30, 0, '2018-08-20 17:00:40.26043+00'),
        ('01CNC4G1Y40000000000000008', 139, 99, 30, 99, '2018-08-20 17:00:47.583501+00');
INSERT 0 15

Certain values of the type column indicate emergencies. I can use this simple query to get a count of how many tasks had each type of emergency occur during their performance:

SELECT COUNT(DISTINCT correlation_id), type
FROM events
WHERE type IN (3, 5, 97, 98)
GROUP BY type;
count type
2 3
2 98
SELECT 2

I can also use this simple query to get a count of how many tasks had any type of emergency occur during their performance:

SELECT COUNT(DISTINCT correlation_id)
FROM events
WHERE type IN (3, 5, 97, 98);
count
3
SELECT 1

The question is this: is there a way to combine these two queries so that I can get the total and also the breakdown by type in a single query?

I cannot just add up the count column, because a correlation_id value can have more than one emergency type associated with it. In the example above, there are three total correlation_id values with emergencies, but adding the count column would come up to four.

fiddle

2 Answers 2

8

What you want is ROLLUP:

SELECT COUNT(DISTINCT correlation_id), type
FROM events
WHERE type IN (3, 5, 97, 98)
GROUP BY ROLLUP (type);
2
  • 1
    For the OP, have a look at GROUP BY GROUPING SETS and GROUP BY CUBE as well. They are variations of GROUP BY ROLLUP and may come in handy in other situations. Dec 21, 2022 at 19:49
  • 1
    I suggest you to read GROUPING SETS, CUBE, and ROLLUP section of the documentation which has simple examples and is easy to read.
    – Sahap Asci
    Dec 21, 2022 at 22:36
0

I would make simple union with some fixed value for all types

SELECT type, COUNT(DISTINCT correlation_id)
FROM events
WHERE type IN (3, 5, 97, 98)
GROUP BY type
UNION
SELECT 0, COUNT(DISTINCT correlation_id)
FROM events
WHERE type IN (3, 5, 97, 98)
order by 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.