1

Given the table like

| userid | active | anonymous | | 1 | t | f | | 2 | f | f | | 3 | f | t |

I need to get:

  • number of users
  • number of users with 'active' = true
  • number of users with 'active' = false
  • number of users with 'anonymous' = true
  • number of users with 'anonymous' = false

with single query.

As for now, I only came out with the solution using union:

SELECT count(*) FROM mytable UNION ALL SELECT count(*) FROM mytable where active UNION ALL SELECT count(*) FROM mytable where anonymous

So I can take first number and find non-active and non-anonymous users with simple deduction .

Is there any way to get rid of union and calculate number of records matching these simple conditions with some magic and efficient query in PostgreSQL 9?

Improve this question
1

2 Answers 2

Reset to default
3

You can use an aggregate function with a CASE to get the result in separate columns:

select count(*) TotalUsers, sum(case when active = 't' then 1 else 0 end) TotalActiveTrue, sum(case when active = 'f' then 1 else 0 end) TotalActiveFalse, sum(case when anonymous = 't' then 1 else 0 end) TotalAnonTrue, sum(case when anonymous = 'f' then 1 else 0 end) TotalAnonFalse from mytable;

See SQL Fiddle with Demo

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I looked at the execution plan and realized, that this query does not use any index which I have on the table, e.g 'create index table_anon on mytable((anonymous=true))'. So it has 'seq scan' and doesn't use any filter.
2

Assuming your columns are boolean NOT NULL, this should be a bit faster:

SELECT total_ct ,active_ct ,(total_ct - active_ct) AS not_active_ct ,anon_ct ,(total_ct - anon_ct) AS not_anon_ct FROM ( SELECT count(*) AS total_ct ,count(active OR NULL) AS active_ct ,count(anonymous OR NULL) AS anon_ct FROM tbl ) sub;

Find a detailed explanation for the techniques used in this closely related answer:
Compute percents from SUM() in the same SELECT sql query

Indexes are hardly going to be of any use, since the whole table has to be read anyway. A covering index might be of help if your rows are bigger than in the example. Depends on the specifics of your actual table.

-> SQLfiddle comparing to @bluefeet's version with CASE statements for each value.

SQL server folks are not used to the proper boolean type of Postgres and tend to go the long way round.

Improve this answer

2 Comments

I knew you would come in with a better postgreSQL answer. I guess I need to learn this DB a bit more.
@bluefeet: I do what I can. ;) There is just too much too learn with the major RDBMS versions alone.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.