Group by multiple criteria

Question

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?

You forgot to mention `NOT NULL` constraints, primary key, data types and other relevant information about your table. Also PostgreSQL 9.? - [First *and* second part of the version number are relevant for major version.](http://www.postgresql.org/support/versioning/) — Erwin Brandstetter, Apr 23 '13 at 01:49

score 3 · Answer 1 · answered Apr 23 '13 at 00:39

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

answered Apr 23 '13 at 00:39

Taryn

242,637
56
362
405

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. – jdevelop Apr 23 '13 at 01:12

score 2 · Accepted Answer · edited May 23 '17 at 10:25

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.

I knew you would come in with a better postgreSQL answer. I guess I need to learn this DB a bit more. — Taryn, Apr 23 '13 at 13:47
@bluefeet: I do what I can. ;) There is just too much too learn with the major RDBMS versions alone. — Erwin Brandstetter, Apr 23 '13 at 13:54

Group by multiple criteria

2 Answers2