clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 462233 distinct fiddles created so far.

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 ??
19 rows affected
 hidden batch(es)


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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
low mid high total_not_null
0 10 6 16
 hidden batch(es)


-- 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
low mid high total
 hidden batch(es)