PostgreSQL comparing one attribute in another relation to another?

Question

Okay, cut a long story short Im working on a project where i need to find out if the capacity of a venue is less than 80% full. Ive got a table called Events and an event has a:

EventID EventName EventCrowd

In another table, ive got Venue as follows: VenueID VenueCapacity VenueStyle

I need to use a JOIN to find out all the modules where the venue will be less than 80% full.

Im a bit stumped on how to do it!

you've given two tables with no relation...hard to join two tables that are not related. Does your Event table have a 'venueID'? Also...is event crowd the number of people currently at the venue (so 80% capacity can be expressed as where eventcrowd / venuecapacity >= .8?) — Twelfth
– Twelfth, Commented Nov 7, 2017 at 23:06
Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Nov 8, 2017 at 14:58
@IanMcannoly - Thats the information we need. It's usually good stack etiquette to edit your question to include these details and not leaving them just in comments. Aimers answer is pretty close to what I'd write, if it's not post a comment and we'll try again. — Twelfth
– Twelfth, Commented Nov 8, 2017 at 20:09
Just to make a note as a professional, I see nothing that could be directly related to your business or company in your post. I see nothing 'sensitive' necessarily, because we don't have information about your employees or anything else. I would argue instead that any sensitive information is nonexistent, because I've done questions like this before. — Thomas Ward
– Thomas Ward, Commented Nov 8, 2017 at 20:48

Aimer · Accepted Answer · 2017-11-08 14:52:35Z

I'm not quite sure I got your question right, but here's an attempt with hardcoded values for demonstration:

SELECT EventsTable.EventID, EventTitle, EventCrowd, Time, Day, VenueCapacity, VenueStyle, EventCrowd/VenueCapacity::float AS PercentCrowd
FROM (
VALUES (1, 'FooFighters', 1000), (2, 'BarFighters', 1000)
) EventsTable(EventID, EventTitle, EventCrowd)
INNER JOIN (
VALUES (1, 1, 2, 'XX:XX', 'XX'), (2, 2, 1, 'YY:YY', 'YY')
) TimeTable(TimeID, EventID, VenueID, Time, Day)
ON EventsTable.EventID = TimeTable.EventID
INNER JOIN (
VALUES (1, 2000, 'Something'), (2, 1100, 'Something Else')
) VenueTable(VenueID, VenueCapacity, VenueStyle)
ON TimeTable.VenueID = VenueTable.VenueID
GROUP BY EventsTable.EventID, EventTitle, EventCrowd, Time, Day, VenueCapacity, VenueStyle
HAVING EventCrowd/VenueCapacity::float < 0.8

As result this will give you

EventID     EventTitle  EventCrowd  Time    Day VenueCapacity   VenueStyle  PercentCrowd
2           BarFighters 1000        YY:YY   YY  2000            Something   0,5

Collectives™ on Stack Overflow

PostgreSQL comparing one attribute in another relation to another?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related