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?

What you want to do is a pivot.

Kermit
– Kermit

2013-03-21 18:23:47 +00:00
Commented Mar 21, 2013 at 18:23 — Kermit
– Kermit, Commented Mar 21, 2013 at 18:23

babbageclunk · Accepted Answer · 2013-03-21 18:35:15Z

6

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).

edited Mar 21, 2013 at 18:35

answered Mar 21, 2013 at 18:27

babbageclunk

8,7911 gold badge36 silver badges37 bronze badges

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

Comments

ruakh · Accepted Answer · 2013-03-21 18:25:10Z

3

I would try

SELECT s.schedule_id,
       COUNT(CASE WHEN su.state = 0 THEN 1 END) AS state0,
       COUNT(CASE WHEN su.state = 1 THEN 1 END) AS state1,
       COUNT(CASE WHEN su.state = 2 THEN 1 END) AS state2,
       COUNT(su.state) AS total
  FROM schedule s
  LEFT
 OUTER
  JOIN schedule_user su
    ON su.schedule_id = s.schedule_id
 GROUP
    BY s.schedule_id
;

answered Mar 21, 2013 at 18:25

ruakh

185k29 gold badges292 silver badges324 bronze badges

Comments

Bohemian · Accepted Answer · 2013-03-21 18:28:18Z

0

Ths standard approach is to use SUM() with a CASE over a JOIN with a GROUP BY:

SELECT 
schedule.schedule_id AS id, 
SUM (case when state=0 then 1 else 0 end) AS state0, 
SUM (case when state=1 then 1 else 0 end) AS state1, 
SUM (case when state=2 then 1 else 0 end) AS state2, 
count(*) AS total
FROM schedule
LEFT JOIN schedule_user
  ON schedule_user.schedule_id = schedule.schedule_id
GROUP BY 1

answered Mar 21, 2013 at 18:28

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

How to query multiple COUNT(*) with good performance

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related