6

There are some similar questions/answers on the forums but I think my problem is simpler.

I have two quesries, eg

SELECT count(*) FROM agent;
SELECT count(*) FROM agent WHERE active = 't';

I would like the output to be on a single row. For bonus points it would be nice to scan through the table only once and update both counters, to get a result like this

active_agents | total_agents
--------------+--------------
    10        |     20

So I guess I have two questions:
What is the Neatest/clearest way to do this, and
What is the fastest way to do it for very large tables.

1
  • 2
    If active is a bool (and I hope it is), you don't actually need = 't'; The bool itself works, and that may be more neat, because that's the goal here. Commented Jun 23, 2017 at 14:36

2 Answers 2

7

You can use CASE statement for this scenario:

SELECT count(*) AS total_agents, 
       SUM(CASE WHEN active = 't' THEN 1 ELSE 0 END) AS active_agents
FROM Agent;

Sample execution with sample data:

CREATE TABLE Agent (Id INT, Active CHAR(10));

INSERT INTO Agent (Active)
VALUES ('a'), ('b'), ('t'), ('c'), ('t'), ('d');

SELECT count(*) AS total_agents, 
       SUM(CASE WHEN active = 't' THEN 1 ELSE 0 END) AS active_agents
FROM Agent;
3
  • 1
    Your field names are backwards - count(*) should be for total agents, conditional sum for active agents.
    – mbeckish
    Commented Jun 23, 2017 at 17:48
  • @mbeckish Thanks for pointing out. Alias name updated.
    – Arulkumar
    Commented Jun 23, 2017 at 17:51
  • And there are several shortenings: count(nullif(active,false)) or sum(active::int)
    – Abelisto
    Commented Jun 23, 2017 at 18:59
5

You can do this a couple of others ways. In addition to Arulkumar's answer

Sample data

CREATE TEMP TABLE foo(x) AS
  VALUES (true),(false),(false),(true),(false);

Using FILTER

I prefer this method because it's syntactically simpler to the SUM() method, but both work the same way.

SELECT
  count(*)                  AS count_total,
  count(*) FILTER (WHERE x) AS count_where_x
FROM foo;
 count_total | count_where_x 
-------------+---------------
           5 |             2
(1 row)

With GROUP BY GROUPING SETS

If you want the results pivoted,

EXPLAIN ANALYZE
SELECT x, count(*)
FROM foo
GROUP BY GROUPING SETS ((x),())
HAVING x IS TRUE
  OR x IS NULL;
 x | count 
---+-------
 t |     2   -- this is where x = true
   |     5   -- this is the total
(2 rows)

Or, if you really want to have fun..

HAVING x IS NOT false;

Because that just sounds cool.

1
  • 1
    falase? Is that intentional?
    – phyrfox
    Commented Jun 23, 2017 at 22:36

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.