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.