How can I get an accurate count of subscribers that are "active" and subscribers that are "inactive/suspended"?
I tried following the documentation, but it's a bit vague on how to match records within a pg_class:
ANALYZE subscribers;
SELECT reltuples AS estimate
FROM pg_class
WHERE relname = 'subscribers'
(and where status = 'active' / where status= 'inactive' or status='suspended')
The above query returns 24, when I want it to return:
estimate (active): 12
estimate (inactive/suspended): 12
Update: It looks like the below query works, but may be a resource intensive. Is there a better alternative to:
SELECT count(*) AS active
FROM subscribers
WHERE status = 'active';
SELECT count(*) AS inactive
FROM subscribers
WHERE status = 'inactive'
OR status = 'suspended';
Table Example:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
DROP TABLE IF EXISTS subscribers;
CREATE TABLE subscribers (
id VARCHAR(36) DEFAULT uuid_generate_v1mc(),
key SERIAL PRIMARY KEY,
status VARCHAR(20) DEFAULT 'inactive',
email VARCHAR,
subscriber VARCHAR NOT NULL,
password VARCHAR,
phone VARCHAR,
plan VARCHAR,
startDate TEXT DEFAULT TO_CHAR(NOW(), 'Mon DD, YYYY'),
endDate TEXT,
amount DECIMAL(12,2),
isGod BOOLEAN DEFAULT FALSE
);
INSERT INTO subscribers (status, email, subscriber, password, phone, plan, endDate, amount)
VALUES
('active', '[email protected]', 'Admin', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Sherry Waters', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Bob Aronssen', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Shaniqua Smith', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Tanya Ballschin', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Siemen Walker', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Jenny Tanks', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Amber Lalampas', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Kyle Teegue', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Gary Pilkinson', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Yasmin Rodrigues', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('active', '[email protected]', 'Adam Johnson', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
('inactive', '[email protected]', 'Carl Sagan', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 3, 2018', 29.99),
('inactive', '[email protected]', 'Mark Canelo', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 12, 2018', 29.99),
('suspended', '[email protected]', 'Axle Root', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 16, 2018', 29.99),
('inactive', '[email protected]', 'Adamn Vicks', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
('inactive', '[email protected]', 'Wes Walls', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
('suspended', '[email protected]', 'Kelly Ullman', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
('inactive', '[email protected]', 'Adam Oates', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
('suspended', '[email protected]', 'Scott Parker', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 21, 2018', 29.99),
('suspended', '[email protected]', 'Emily Loz', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 22, 2018', 29.99),
('inactive', '[email protected]', 'Parker Posey', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 29, 2018', 29.99),
('suspended', '[email protected]', 'Alisha Tallis', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 29, 2018', 29.99),
('suspended', '[email protected]', 'Damien Smith', 'password', '(555) 555-5555', 'Carlotta Prime', 'Jan 29, 2018', 29.99);