PostgreSQL - Set default value on inner with count(*)

Question

I've found nothing about my problem. I have 2 tables like: range(pk, min, max) and user(name, value), I'll inner join and group by to have: for every range, every user, the sum of value (even when there is no result). Like :

range   name    value
0-10    test     2

0-10    test2    8
0-10    test3    3

0-10    test4    7
0-20    test     0 ====> actualy, i don't get this line

0-20    test2    8
0-20    test3    3

0-20    test4    7

Actually my query is:

select cast(range.min AS CHARACTER VARYING) || '-' || cast(range.max AS CHARACTER VARYING)
    ,user.NAME
    ,count(*)
FROM range
INNER JOIN user ON user.value >= range.min
    AND user.value <= range.max
GROUP BY range.pk
    ,user.NAME
ORDER BY range.pk;

Does anyone have an idea to insert 0's when there is no result?

EDIT : My range is never NULL, i'll just add user name when he is not in range.

Might be a duplicate of https://stackoverflow.com/questions/27800119/postgresql-case-end-with-multiple-conditions — Héctor Álvarez, Jan 05 '18 at 12:48
Possible duplicate of [PostgreSQL CASE ... END with multiple conditions](https://stackoverflow.com/questions/27800119/postgresql-case-end-with-multiple-conditions) — Héctor Álvarez, Jan 05 '18 at 12:48
the outer join add me the missing range, but not all the user name with 0. I'll try the Case...END — yeticoolbrothers42, Jan 05 '18 at 12:55

score 1 · Accepted Answer · answered Jan 05 '18 at 13:16

1

This is a bit confusing because there is a table called user with duplicate names. So either this is not the user table (with one record per user) or we are not really interested in users here but in user names (and multiple users can have the same name).

Anyway: You want to count zero when the user doesn't match the range. This means you want to combine all users with all ranges and then count. Do this with a cross join.

select
  r.min || '-' || r.max as value_range,
  u.name as user_name,
  (
    select count(*) 
    from users u2 
    where u2.name = u.name 
    and u2.value between r.min and r.max
  ) as user_range_count
from range r
cross join (select distinct name from user) u
order by r.min, r.max, u.name;

answered Jan 05 '18 at 13:16

Thorsten Kettner

89,309
7
49
73

i'll try, but on my user table, multiple users can have the same name, – yeticoolbrothers42 Jan 05 '18 at 13:19
that's WORKS, Big tanks !! – yeticoolbrothers42 Jan 05 '18 at 13:27
How i can do the same with not a range table ? (on my user table, i've a column GENDER, its 0 when not specify, 1 when it's men and 2 when it's women) – yeticoolbrothers42 Jan 05 '18 at 15:50
Then you should also have a gender table. – Thorsten Kettner Jan 05 '18 at 17:20
Done, i've create a temp table to make your answer. Big thanks – yeticoolbrothers42 Jan 05 '18 at 18:20

PostgreSQL - Set default value on inner with count(*)

1 Answers1