Create a report with 2 rows of values, each of which is from a separate SELECT statement

Question

I have a report (using Blazer, if you care) that displays data like this, of recently updated or created rows in the jobs table:

5 Minutes | 1 Hour | 1 Day | Total
----------------------------------
        0        0     367  30,989

The SQL looks something like this:

SELECT
    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()
    ) as "5 Minutes",
    
    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW()
    ) as "1 Hour",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW()
    ) as "1 Day",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    ) as "Total"
;

I want to add a second row, for jobs WHERE "Jobs"."active" IS TRUE. How do I make this display another row?

I want the final result to be something like this:

Status | 5 Minutes | 1 Hour | 1 Day | Total
-------------------------------------------
*                0        0     367  30,989
Active           0        0     123  24,972

The labels are not the issue. The only thing that's not obvious is how to create a new row.

score 0 · Answer 1 · answered Jun 22 '21 at 20:36

The simplest way is to UNION on another bunch of queries, that have this more restrictive where clause:

SELECT
    '*' as Kind,

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()
    ) as "5 Minutes",
    
    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW()
    ) as "1 Hour",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW()
    ) as "1 Day",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    ) as "Total"

UNION ALL

SELECT
    'Active', 

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE AND "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()
    ) as "5 Minutes",
    
    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE AND "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW()
    ) as "1 Hour",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE AND "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW()
    ) as "1 Day",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE 
    ) as "Total"

score 0 · Answer 2 · answered Jun 22 '21 at 20:39

If I were you, I would prefer this way to resolve your query:

select
  "Jobs"."active" as Status,
  sum(case when "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW() then 1 else 0 end) as "5 Minutes",
  sum(case when "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW() then 1 else 0 end) as "1 Hour",
  sum(case when "Jobs"."updated_at" "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW() then 1 else 0 end) as "1 Day",
  count(*) as "Total"
from public.jobs AS "Jobs"
group by "Jobs"."active"

This way you read your table public.jobs once, and not several times (once per count). With this choice, grouping by the status is a simple group by operation

score 0 · Answer 3 · answered Jun 22 '21 at 21:59

Basically, you want conditional aggregation. In Postgres, that would normally use filter:

SELECT COUNT(*) FILTER (WHERE j."updated_at" BETWEEN NOW() - INTERVAL '5 minute' AND NOW()) as cnt_5_minutes,
       COUNT(*) FILTER (WHERE j."updated_at" BETWEEN NOW() - INTERVAL '1 hour' AND NOW()) as cnt_1_hour,
       COUNT(*) FILTER (WHERE j."updated_at" BETWEEN NOW() - INTERVAL '1 day' AND NOW()) as cnt_1_day,
       COUNT(*) as Total
FROM public.jobs j;

You probably don't have future update dates, so this would more simply be written as:

SELECT COUNT(*) FILTER (WHERE j."updated_at" >= NOW() - INTERVAL '5 minute') as cnt_5_minutes,
       COUNT(*) FILTER (WHERE j."updated_at" >= NOW() - INTERVAL '1 hour') as cnt_1_hour,
       COUNT(*) FILTER (WHERE j."updated_at" >= NOW() - INTERVAL '1 day') as cnt_1_day,
       COUNT(*) as Total
FROM public.jobs j;

In addition, I would advise you to drop the double quotes from updated_at. Using double quotes around identifiers is just a bad habit.

score 0 · Answer 4 · answered Jun 23 '21 at 00:11

The only thing that's not obvious is how to create a new row.

Basically, add a second row with UNION ALL.

First get rid of all the separate SELECT queries for each metric, though. That's needlessly expensive (important if the table is not trivially small). A single SELECT with conditional aggregates can replace all of your original (like Gordon suggested). In Postgres 9.4 or later, the aggregate FILTER clause is the way to go. See:

Aggregate columns with additional (distinct) filters

To get another row you could just run a another query adding the filter "active" IS TRUE to each expression (which boils down to just active, as a boolean column needs no further evaluation).

But that would double the cost again, and we can avoid that. Run a single SELECT in a CTE, and the split results with UNION ALL in the outer query:

WITH cte AS (
   SELECT count(*) FILTER (WHERE updated_at > now() - interval '5 min')             AS ct_5min
        , count(*) FILTER (WHERE updated_at > now() - interval '5 min' AND active)  AS ct_5min_a
        , count(*) FILTER (WHERE updated_at > now() - interval '1 hour')            AS ct_1h
        , count(*) FILTER (WHERE updated_at > now() - interval '1 hour' AND active) AS ct_1h_a
        , count(*) FILTER (WHERE updated_at > now() - interval '1 day')             AS ct_1d
        , count(*) FILTER (WHERE updated_at > now() - interval '1 day' AND active)  AS ct_1d_a
        , count(*)                       AS ct_all
        , count(*) FILTER (WHERE active) AS ct_all_a
   FROM   public.jobs
   )
SELECT '*' AS status, ct_5min, ct_1h, ct_1d, ct_all
FROM   cte
UNION ALL
SELECT 'Active', ct_5min_a, ct_1h_a, ct_1d_a, ct_all_a
FROM   cte

Create a report with 2 rows of values, each of which is from a separate SELECT statement

4 Answers4