There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday.

The desired result is to count the number of users that are present for at least one period per day of the work week. For example, if a user is present two quarters of a day, that counts as one.

This is what I thought would work for a Monday, but it's incorrect:

 count(CASE WHEN TRUE = ANY(ARRAY_AGG(mon)) THEN 1 ELSE null END) as mon,
FROM user_table 
LEFT JOIN schedule_table ON user_table.id = schedule_table.user_id
GROUP BY user_id

I created a SQLFiddle to demonstrate.


I was looking for an summary table like this

| Mon | Tue | Wed | Thu | Fri |
|   4 |   3 |   4 |   4 |   4 |

I am running PostgreSQL 9.3

Here are the table definitions I am using:

CREATE TABLE user_table
  id integer,
  start_dt date

CREATE TABLE schedule_table
  id integer,
  user_id integer,
  mon boolean,
  tue boolean,
  wed boolean,
  thu boolean,
  fri boolean
  • Proper table definitions (\d tbl in psql) and your version of Postgres would help. Feb 14, 2015 at 23:05

1 Answer 1


All users present on Monday:

   SELECT user_id
   FROM   schedule_table 
   GROUP  BY 1
   HAVING bool_or(mon)
   ) s
JOIN   user_table u ON u.id = s.user_id;

The aggregate function bool_or() comes in handy.

Summary table:

SELECT count(mon OR NULL) AS mon
     , count(tue OR NULL) AS tue
     , count(wed OR NULL) AS wed
     , count(thu OR NULL) AS thu
     , count(fri OR NULL) AS fri
   SELECT user_id
        , bool_or(mon) AS mon
        , bool_or(tue) AS tue
        , bool_or(wed) AS wed
        , bool_or(thu) AS thu
        , bool_or(fri) AS fri
   FROM   schedule_table 
   GROUP  BY 1
   ) s;

Result as desired:

mon tue wed thu fri
4   3   4   4   4

Note that I didn't join to user_table at all here. It's not needed.

SQL Fiddle.

About counting techniques:

However, your database design is odd, schedule_table has no information to signify the period of the day. And why bundle attributes for the same period on all days in one row?

Either, to store explicit information:

CREATE TABLE schedule (
  schedule_id int PRIMARY KEY
, schedule text NOT NULL

  (11, 'mon, period 1')
  (12, 'mon, period 2')
  (13, 'mon, period 3')
  (14, 'mon, period 4')
  (21, 'tue, period 1')
  (22, 'tue, period 2')
  -- etc.

CREATE TABLE user_schedule (
  user_schedule_id integer,
  user_id int REFERENCES users,
  schedule_id int REFERENCES schedule

Only enter rows for periods with presence.
Or, to optimize storage:

  user_id serial PRIMARY KEY
, start_dt date
, presence_bitmap bit(20)

INSERT INTO users (start_dt, presence_bitmap) VALUES
 ('2015-02-14', '11111111111111111111'),
 ('2015-02-14', '00000000000000001111'); -- etc.

Only needs a fraction of the storage on disk.

  • Thank you. It would be difficult to change the data model at the moment, but your suggestions are much better and I wish I would have posed the question when we built the model. I think your suggestions will really help others who might face a similar design choice. There's actually another table to label the times of the day, which are customizable for each user's organization. Too complicated, really.
    – JHo
    Feb 15, 2015 at 19:31
  • @JHo: I added some more. Feb 16, 2015 at 18:11
  • Wow. The COUNT is much faster than SUM(CASE WHEN...). My query on production data went from 194ms to 119ms. Still slow but almost 40% faster. Thanks again.
    – JHo
    Feb 16, 2015 at 20:15
  • @JHo: The "summary" query has to read the entire table, so performance mainly depends on the size of the table (as long as the rest is optimized). Feb 16, 2015 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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