PostgreSQL select statement

Question

Given the following tables:

 shows:

      title      | basic_ticket_price
 ----------------+--------------------
  Inception      |              $3.50
  Romeo & Juliet |              $2.00

performance:

  perf_date  | perf_time |     title
 ------------+-----------+----------------
  2012-08-14 | 00:08:00  | Inception
  2012-08-12 | 00:12:00  | Romeo & Juliet

booking:
  ticket_no | perf_date  | perf_time | row_no | person_id
 -----------+------------+-----------+--------+-----------
          1 | 2012-08-14 | 00:08:00  | P01    | 1
          2 | 2012-08-12 | 00:12:00  | O05    | 4
          3 | 2012-08-12 | 00:12:00  | A01    | 2

And an additional table: seat which contains a list of seats numbered like that of row_no in booking with an area name.

Having grouped the booked seats using this statement:

select count(row_no) AS row_no,
       area_name 
from seat 
where exists (select row_no 
              from   booking 
              where booking.row_no = seat.row_no) 
group by area_name;

which produces:

    row_no |  area_name
   --------+--------------
         1 | rear stalls
         2 | front stalls

How can I now use the counted rows and area_name to write a single SQL statement to produce a list showing the names of shows, dates and times of performances, and the number of booked seats in each area?

I've tried this:

 select s.title,
        perf_date,
        perf_time,
        count(row_no) AS row_no,
        area_name 
 from shows s, 
      performance,
      seat 
 where exists (select row_no 
               from booking 
               where booking.row_no =  seat.row_no) 
 group by area_name,s.title,performance.perf_date,performance.perf_time;

But it shows repeated rows:

      title      | perf_date  | perf_time | row_no |  area_name
 ----------------+------------+-----------+--------+--------------
  Romeo & Juliet | 2012-08-12 | 00:12:00  |      1 | rear stalls
  Romeo & Juliet | 2012-08-14 | 00:08:00  |      2 | front stalls
  Inception      | 2012-08-12 | 00:12:00  |      1 | rear stalls
  Inception      | 2012-08-14 | 00:08:00  |      2 | front stalls
  Inception      | 2012-08-14 | 00:08:00  |      1 | rear stalls
  Inception      | 2012-08-12 | 00:12:00  |      2 | front stalls
  Romeo & Juliet | 2012-08-14 | 00:08:00  |      1 | rear stalls
  Romeo & Juliet | 2012-08-12 | 00:12:00  |      2 | front stalls
  (8 rows)

Any help with solving this would be appreciated.

Also: you are missing a join condition in your second statement. It will produce a cartesion join between shows, performance and seat. Youd'd better rewrite that using JOIN ... — user330315
– user330315, Commented Mar 25, 2012 at 11:41
please accept my apologies, i am using psql but tagged it also mysql as i have found the majority of statements to be similar espescially the select statements. — Ben Crazed Up Euden
– Ben Crazed Up Euden, Commented Mar 25, 2012 at 11:47
You should use a serial as primary key. What if you get a second room for showing films? You can't let two films happen at the same time, because they are identified by perf_date and perf_time. Or what to do, when a performance is scheduled? You have to modify multiple tables, which is a bad thing. And a minor suggestion: I wouldn't repeat the table title in column names. Else you end with perf.perf_title - perf.title would be good enough. — user unknown
– user unknown, Commented Mar 25, 2012 at 13:15
@userunknown: perf_date is actually an acceptable choice for an identifier. You wouldn't want to abuse a type name as column name. Apart from that, it really should be a timestamp. I'll add an answer. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 25, 2012 at 19:25

Erwin Brandstetter · Accepted Answer · 2012-03-25 20:07:18Z

You should consider merging the columns perf_date date and perf_time time into a single timestamp column:

perf_timestamp timestamp

If you need a date or time from a timestamp, simply cast it like this:

SELECT perf_timestamp::time;
SELECT perf_timestamp::date;

I would generally suggest to use surrogate primary keys. The name of a "show" (movie title) is no natural key - it is not unique. Or, as @user_unknown already mentioned: the start time is no practical primary key for a performance. You could use serial columns for that. The whole setup could look something like this:

 -- show:
CREATE TEMP TABLE show (
 show_id serial PRIMARY KEY
,title text
,basic_ticket_price money  -- or numeric
);
INSERT INTO show (title, basic_ticket_price) VALUES
 ('Inception', 3.50)
,('Romeo & Juliet', 2.00);

-- performance:
CREATE TEMP TABLE performance (
 performance_id serial PRIMARY KEY
,show_id int REFERENCES show(show_id) ON UPDATE CASCADE
,perf_start timestamp
);
INSERT INTO performance (show_id, perf_start) VALUES
 (1, '2012-08-14 00:08')
,(2, '2012-08-12 00:12');

-- seat:
CREATE TEMP TABLE seat (
 row_no text PRIMARY KEY
,area_name text
);
INSERT INTO seat (row_no, area_name) VALUES
 ('P01', 'rear stalls')
,('O05', 'front stalls')
,('A01', 'front stalls');

-- booking:
CREATE TEMP TABLE booking (
 ticket_id serial PRIMARY KEY
,performance_id int REFERENCES performance(performance_id) ON UPDATE CASCADE
,row_no text REFERENCES seat(row_no) ON UPDATE CASCADE
,person_id int -- REFERENCES ?
);
INSERT INTO booking (performance_id, row_no, person_id) VALUES
 (1, 'P01', 1)
,(2, 'O05', 4)
,(2, 'A01', 2);

Then your query could look like this:

SELECT p.perf_start
      ,sh.title
      ,s.area_name
      ,count(*) booked
FROM   booking     b
JOIN   seat        s USING (row_no)
JOIN   performance p USING (performance_id)
JOIN   show        sh USING (show_id)
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

Result:

 perf_start          | title          | area_name    | booked
---------------------+----------------+--------------+-------
 2012-08-12 00:12:00 | Romeo & Juliet | front stalls | 2
 2012-08-14 00:08:00 | Inception      | rear stalls  | 1

DRapp · Accepted Answer · 2012-03-25 13:24:49Z

0

On a per performance entry, find all bookings. Of those bookings, link to the seats table to determine the area name. Then, simple COUNT(*) grouped by the criteria per show / area.

SELECT
      p.perf_date,
      p.perf_time,
      p.title,
      s.area_name,
      COUNT(*) as SeatsSold
   from
      performance p

         JOIN booking b
            ON p.perf_date = b.perf_date
            AND p.perf_time = b.perf_time

            JOIN seat s
               ON b.row_no = s.row_no
   group by
      p.perf_date,
      p.perf_time,
      p.title,
      s.area_name

answered Mar 25, 2012 at 13:24

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Comments

Holger Just · Accepted Answer · 2012-03-25 11:40:54Z

0

Your rows don't repeat. Notice the differences in the date and area_name in your results. It all looks just fine to me.

answered Mar 25, 2012 at 11:40

Holger Just

56.5k15 gold badges124 silver badges137 bronze badges

Collectives™ on Stack Overflow

PostgreSQL select statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related