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 stackoverflow.com/questions/27800119/… — Héctor Álvarez
– Héctor Álvarez, Commented Jan 5, 2018 at 12:48
Possible duplicate of PostgreSQL CASE ... END with multiple conditions — Héctor Álvarez
– Héctor Álvarez, Commented Jan 5, 2018 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
– yeticoolbrothers42, Commented Jan 5, 2018 at 12:55

Thorsten Kettner · Accepted Answer · 2018-01-05 13:16:36Z

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 5, 2018 at 13:16

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

5 Comments

yeticoolbrothers42 Over a year ago

i'll try, but on my user table, multiple users can have the same name,

yeticoolbrothers42 Over a year ago

that's WORKS, Big tanks !!

yeticoolbrothers42 Over a year ago

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)

Thorsten Kettner Over a year ago

Then you should also have a gender table.

yeticoolbrothers42 Over a year ago

Done, i've create a temp table to make your answer. Big thanks

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related