Multiple COUNTs over the same column

Question

I have a table (PostgreSQL 9.6) containing 2.6 million+ timestamped rows associated with account identifiers, and for any given identifier, I want to count the total number of occurrences as well as just the number of occurrences today in a single query.

For reference, this is the same table described in this question, but I have simplified it here to focus on this specific issue:

CREATE TABLE account_test
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE log_test
(
  account integer NOT NULL REFERENCES account_test(id),
  event_time timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX account_test_idx ON log_test USING btree (account,event_time);

INSERT INTO account_test VALUES (1);
INSERT INTO account_test VALUES (2);

INSERT INTO log_test VALUES (1,'2018-01-01');
INSERT INTO log_test VALUES (1,'2018-01-02');
INSERT INTO log_test VALUES (1,'2018-01-03');
INSERT INTO log_test VALUES (1,now());
INSERT INTO log_test VALUES (1,now());

INSERT INTO log_test VALUES (2,'2018-01-01');
INSERT INTO log_test VALUES (2,'2018-01-02');
INSERT INTO log_test VALUES (2,now());

This is my initial attempt, which is producing the same number for both the daily and total counts due to the GROUP BY:

    SELECT a.id,COUNT(d) AS daily,COUNT(t) AS total FROM account_test a 
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id;

 id | daily | total
----+-------+-------
  1 |    10 |    10
(1 row)

The results I am looking for are:

 id | daily | total
----+-------+-------
  1 |     2 |     5
(1 row)

Specifically, the result of this ugly query:

SELECT qd.id,qd.daily,qt.total FROM
(
    SELECT a.id,COUNT(d) AS daily FROM account_test a 
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
     WHERE a.id=1 GROUP BY a.id
) qd,
(
    SELECT a.id,COUNT(t) AS total FROM account_test a 
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id
) qt;

I realize this may be a softball question, but in this case my SQL instincts are failing me, and I suspect there might be some clever trick that would eliminate the extra JOIN.

Answer 1

I believe using a SUM + CASE expression would work, because the CASE enables you to do a 'selective count'. Or you can use the newer FILTER:

SELECT a.id,
  count(*) FILTER (WHERE d.event_time > now() - interval '1 day') AS daily,      
  count(*) AS total
FROM account_test a 
JOIN log_test d ON a.id=d.account
GROUP BY a.id;

With the dataset from your question, this gives the following results:

 id | daily | total
----+-------+-------
  2 |    1  |    3
  1 |    2  |    5

Answer 2

This approach is without using FILTER

SELECT 
  a.id,
  sum(if(d.event_time > now() - interval '1 day',1,0)) AS daily,      
  count(*) AS total
FROM account_test a 
JOIN log_test d ON a.id=d.account
GROUP BY a.id;