CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

PostgreSQL count of records by status

In my PostgreSQL database I have users table which has the following columns:

id - integer
email - string
blocked - boolean

Now I want to have sql query that returns me following results:

total:                blocked:                unblocked
total count of users  count of blocked users  count of users that are not blocked

How can I do that in PostgreSQL?

Upvotes: 3

Views: 1452

Answers (2)

klin
klin

Reputation: 121604

Use count() with filter:

select
    count(*) as total,
    count(*) filter (where blocked) as blocked,
    count(*) filter (where not blocked) as unblocked
from users

Upvotes: 3

etsa
etsa

Reputation: 5060

You can try this:

SELECT COUNT(ID) AS USER_RC
     , SUM(CASE WHEN BLOCKED=TRUE THEN 1 ELSE 0 END) AS BLOCKED_RC
     , SUM(CASE WHEN BLOCKED=TRUE THEN 0 ELSE 1 END) AS UNBLOCKED_RC  
FROM TX1;

or if you prefer (it uses only two aggregation functions):

SELECT A.*, USER_RC-BLOCKED_RC AS UNBLOCKED_RC
FROM (SELECT COUNT(ID) AS USER_RC, SUM(CASE WHEN BLOCKED=TRUE THEN 1 ELSE 0 END) AS BLOCKED_RC 
     FROM TX1) A
;

Sample data:

INSERT INTO TX1 VALUES (1,'aaa',FALSE);
INSERT INTO TX1 VALUES (2,'bbb',TRUE);
INSERT INTO TX1 VALUES (3,'ccc',TRUE);
INSERT INTO TX1 VALUES (4,'ddd',TRUE);
INSERT INTO TX1 VALUES (5,'eee',FALSE);

Output:

user_rc blocked_rc  unblocked_rc
    5       3           2

Upvotes: 3

Related Questions