Doing two counts on a single table in a single query

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;

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.

Tags:

Postgresql