In PostgreSQL, get a minimum value if there is none

Question

Sorry if the title did not match the actual question. I'm not sure how to phrase this.

In Postgres, I have users that can be at a station. The stations are numbered 1 to 5. I have a table users and it has a column station. If a user is at a station, station value is the number of the station, if not, it is NULL. Here's how I find the number of users at each station:

SELECT station, count(users) AS users
    FROM users
    WHERE station IS NOT NULL
    GROUP BY station ORDER BY station;

This yields:

 station | users 
---------+---------
       1 |     4
       2 |     1
       3 |     1

But that's not really true. There are zero users on station 4 and 5. How can I address that? I would like to use this query as a view to calculate other things, but I need all the stations there. I don't need a table for the stations, as far as I know: I'm not going to use them anywhere else.

So how do I account for the unused stations? Do I really need to make a table for them and reference the stations table from the users table or is there a simpler way?

Gordon Linoff · Accepted Answer · 2015-06-03 01:30:52Z

4

Assuming that you have a table stations, you can handle this with a left join:

SELECT s.station, count(u.station) AS users
FROM stations s LEFT JOIN
     users u
     ON s.station = u.station
GROUP BY s.station
ORDER BY s.station;

Without such a table, you can use generate_series():

SELECT s.station, count(u.station) AS users
FROM generate_series(1, 5) as s(station) LEFT JOIN
     users u
     ON s.station = u.station
GROUP BY s.station
ORDER BY s.station;

edited Jun 3, 2015 at 1:30

answered Jun 3, 2015 at 1:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

zerkms Over a year ago

You should COUNT(u.station) instead of count(users) (in the first query)

Gordon Linoff Over a year ago

@zerkms . . . I agree. In fact, I'm guessing the users.users isn't really a field, so the OP has a typo in the original question. (S/he claims that count(u.users) produces valid output.)

zerkms Over a year ago

Yep, I also rely on the fact that OP's query is correct (even though it does look weird) :-)

eje211 Over a year ago

Thanks! I did not know about generate_series(). But I suppose it's what I needed!

Collectives™ on Stack Overflow

In PostgreSQL, get a minimum value if there is none

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related