How to query multiple COUNT(*) with good performance

Question

I have this table:

CREATE TABLE schedule (
schedule_id serial NOT NULL,
start_date date,
CONSTRAINT schedule_id PRIMARY KEY (schedule_element_id)
)

And this table:

CREATE TABLE schedule_user (
schedule_user_id serial NOT NULL,
schedule_id integer,
state int,
CONSTRAINT fk_schedule_id   FOREIGN KEY (schedule_id)
      REFERENCES schedule (schedule_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

schedule 
 -------------------------
|schedule_id |date        |
|------------+------------|
|1           |'2013-10-10'|
|2           |'2013-10-20'|
|3           |'2013-08-13'|
 -------------------------

schedule_user 
 -----------------------------------
|schedule_user_id|schedule_id |state|
|----------------+------------+-----|
|1               | 1          |0    |
|2               | 1          |1    |
|3               | 1          |2    |
|4               | 1          |0    |
|5               | 1          |1    |
|6               | 1          |1    |
|4               | 2          |0    |
|5               | 2          |1    |
|7               | 2          |0    |
|2               | 3          |1    |
 -----------------------------------

And I want a table like this:

characteristic
 ---------------------------------------
|schedule_id |state0|state1|state2|total|
|------------+------+------+------+-----|
|1           |2     |3     |1     |6    |
|2           |2     |1     |0     |3    |
|3           |1     |1     |0     |2    |
 ---------------------------------------

I've made this query that looks as as horrible as it's performance.

SELECT 
    schedule.schedule_id AS id, 
    (( SELECT count(*) AS count
         FROM schedule_user
         WHERE schedule_user.schedule_id = schedule.schedule_id
           AND state=0))::integer AS state0, 
    (( SELECT count(*) AS count
         FROM schedule_user
         WHERE schedule_user.schedule_id = schedule.schedule_id
           AND state=1))::integer AS state1,
    (( SELECT count(*) AS count
         FROM schedule_user
         WHERE schedule_user.schedule_id = schedule.schedule_id
           AND state=2))::integer AS state2,          
    (( SELECT count(*) AS count
         FROM schedule_user
         WHERE schedule_user.schedule_id = schedule.schedule_id))::integer
       AS total
  FROM schedule

Is there a better way to perform such a query? Should I create an Index to 'state' column? if so, how should it look like?

Answer 1

You want to make a pivot table. An easy way to make one in SQL if you know all of the possible values of state beforehand is using sum and case statements.

select schedule_id,
       sum(case state when 0 then 1 else 0 end) as state0,
       sum(case state when 1 then 1 else 0 end) as state1,
       sum(case state when 2 then 1 else 0 end) as state2,
       count(*) as total
from schedule_user
group by schedule_id;

Another way is to use the crosstab table function.

Neither of these will let you get away with not knowing the set of values of state (and hence the columns in the result set).