2

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', 'admin@admin.com', 'Admin', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'squatters@gmail.com', 'Sherry Waters', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'bob-eh@sap.com', 'Bob Aronssen', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'shani.smith@hotmail.com', 'Shaniqua Smith', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'tanyaballschin@gmail.com', 'Tanya Ballschin', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'lukeskywalker@rebelforce.com', 'Siemen Walker', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'jTank@aol.com', 'Jenny Tanks', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'amberLamps@yahoo.com', 'Amber Lalampas', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'kylebTeegue@gmail.com', 'Kyle Teegue', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'snakePiliskin@gmail.com', 'Gary Pilkinson', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'yasminRod@hotmail.com', 'Yasmin Rodrigues', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('active', 'adaDamn@photonmail.com', 'Adam Johnson', 'password', '(555) 555-5555', 'Carlotta Prime', null, 29.99),
  ('inactive', 'carlsagan42@yahoo.com', 'Carl Sagan', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 3, 2018', 29.99),
  ('inactive', 'seamark@outlook.com', 'Mark Canelo', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 12, 2018', 29.99),
  ('suspended', 'axxll@manjaro.com', 'Axle Root', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 16, 2018', 29.99),
  ('inactive', 'vicksAdam@sap.com', 'Adamn Vicks', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
  ('inactive', 'wallyworld@manjaro.com', 'Wes Walls', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
  ('suspended', 'kellyUll@gmail.com', 'Kelly Ullman', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
  ('inactive', 'oatesA@aol.com', 'Adam Oates', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 17, 2018', 29.99),
  ('suspended', 'scottParker@jaro.com', 'Scott Parker', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 21, 2018', 29.99),
  ('suspended', 'asmLossenger@mancusco.com', 'Emily Loz', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 22, 2018', 29.99),
  ('inactive', 'pparks@akins.com', 'Parker Posey', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 29, 2018', 29.99),
  ('suspended', 'aleashtrails@kilmas.com', 'Alisha Tallis', 'password', '(555) 555-555', 'Carlotta Prime', 'Jan 29, 2018', 29.99),
  ('suspended', '88Damon@photonmail.com', 'Damien Smith', 'password', '(555) 555-5555', 'Carlotta Prime', 'Jan 29, 2018', 29.99);
  • Unrelated, but: having a uuid and a serial seems overkill. – a_horse_with_no_name Mar 17 '18 at 8:43
  • This "key" is required and used by a React UI library. If the key is missing from the record, it'll complain. Otherwise, I would just have the id. – Matt Carlotta Mar 17 '18 at 16:46
2

You can use conditional aggregation:

SELECT count(*) filter (where status = 'active') AS active, 
       count(*) filter (where status in ('inactive', 'suspended')) as inactive
FROM subscribers
  • Bench marked all suggestions and this one appears to be the quickest query. – Matt Carlotta Mar 17 '18 at 18:09
1

Something like:

select case when status = 'active'
         then 'estimate (active):'
         else 'estimate (inactive/suspended):'
       end S,
       count(*)
  from subscribers group by S;


               s                | count 
--------------------------------+-------
 estimate (active):             |    12
 estimate (inactive/suspended): |    12
(2 rows)

Or something that approaches your layout more closely:

     select case when status = 'active'
             then 'estimate (active): '
             else 'estimate (inactive/suspended): '
           end ||
           count(*) estimatum
      from (select case when status = 'active'
                     then status else 'X' end status
              from subscribers) as S
      group by  status;

             estimatum         
-----------------------------------
 estimate (active): 12
 estimate (inactive/suspended): 12
(2 rows)
1

"…the below query works, but may be a resource intensive. Is there a better alternative…"

I think you mean you want to avoid a full scan of the table. It is possible to extract an estimate from the planner (credit to Michael Fuhr):

CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS
$func$
DECLARE
    rec   record;
    ROWS  INTEGER;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN ROWS IS NOT NULL;
    END LOOP;

    RETURN ROWS;
END
$func$ LANGUAGE plpgsql;
select count_estimate($$select * from subscribers where status='active'$$);
| count_estimate |
| -------------: |
|             12 |
select count_estimate($$select * from subscribers where status='inactive' or status='suspended'$$);
| count_estimate |
| -------------: |
|             10 |

dbfiddle here

0

In your case

SELECT count(*), status FROM subscribers GROUP BY status;

this query will give you counts for active, inactive and suspended status.

Result will be status, status_count format

As long as you don't have any other status, this query is ok.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.