Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE foo(foo_id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, score int);
INSERT INTO foo(score) VALUES
  (4), (5), (4), (5), (6)
, (8), (9), (4), (6), (8)
, (4), (6), (8), (9), (5), (9)
, (NULL), (NULL), (NULL);  -- can there be NULL ??

CREATE TABLE



INSERT 0 19


SELECT count(*) FILTER (WHERE score BETWEEN 0 AND 3)  AS low
     , count(*) FILTER (WHERE score BETWEEN 4 AND 7)  AS mid
     , count(*) FILTER (WHERE score BETWEEN 8 AND 10) AS high
     , count(*)                                       AS total  -- adds up, unless NULL or other values are involved
     , count(score)                                   AS total_not_null
FROM   foo;


low
mid
high
total
total_not_null


0
10
6
19
16



SELECT 1


-- THE REST ALSO WORKS IN POSTGRES 9.3 OR OLDER

-- Erwin
SELECT count(score BETWEEN 0 AND 3  OR NULL) AS low
     , count(score BETWEEN 4 AND 6  OR NULL) AS mid
     , count(score BETWEEN 7 AND 10 OR NULL) AS high
     , count(*)                              AS total
     , count(score)                          AS total_not_null
FROM   foo;


low
mid
high
total
total_not_null


0
10
6
19
16



SELECT 1


-- Phil
select sum(case when score between 0 and  3 then 1 else 0 end) as low,
       sum(case when score between 4 and  6 then 1 else 0 end) as mid,
       sum(case when score between 7 and 10 then 1 else 0 end) as high,
       sum(1) as total
from   foo;

-- You could exclude NULL values with an added WHERE clause
select sum(case when score between 0 and  3 then 1 else 0 end) as low,
       sum(case when score between 4 and  6 then 1 else 0 end) as mid,
       sum(case when score between 7 and 10 then 1 else 0 end) as high,
       sum(1) as total_not_null
from   foo WHERE score IS NOT NULL;


low
mid
high
total


0
10
6
19



SELECT 1




low
mid
high
total_not_null


0
10
6
16



SELECT 1


-- The same without rows:

-- Erwin
SELECT count(score < 4 OR NULL)             AS low
     , count(score BETWEEN 4 AND 6 OR NULL) AS mid
     , count(score > 6 OR NULL)             AS high
     , count(*) AS total
FROM   foo
WHERE  FALSE;  -- to demonstrate "no rows"

-- Phil
select sum(case when score between 0 and  3 then 1 else 0 end) as low,
       sum(case when score between 4 and  6 then 1 else 0 end) as mid,
       sum(case when score between 7 and 10 then 1 else 0 end) as high,
       sum(1) as total
from   foo
WHERE  FALSE;  -- to demonstrate "no rows"


low
mid
high
total


0
0
0
0



SELECT 1




low
mid
high
total


null
null
null
null



SELECT 1