How do I use SQL to create a virtual column that sums the number of seats per row?

CANNOT ADD VIRTUAL COLUMN WITH THE PROPER CONDITIONS

Using sql, I was tasked to create a database representing an ER diagram. I have two tables that I believe are specific to the question I have to ask:

• table 1: "seat" and
• table 2:"row"

I wish to write SQL for the question
which is:

> Add a virtual column to calculate the total number of seats in each row.

The creation of the tables in question is as follows:

CREATE TABLE "row"(
"rowID" NUMBER(5) PRIMARY KEY,
numbers NUMBER(5) NOT NULL,
seats NUMBER(5) NOT NULL,
auditoriumID NUMBER(5),
CONSTRAINT row_aud_fk FOREIGN KEY(auditoriumID) REFERENCES auditorium(auditoriumID));

CREATE SEQUENCE seq_row
START WITH 10
INCREMENT BY 1
MAXVALUE 100
NOCYCLE
CACHE 5;

CREATE TABLE seat(
seatID NUMBER(5) PRIMARY KEY,
"rowID" NUMBER(5),
numbers NUMBER(5) NOT NULL,
"name" NVARCHAR2(26) NOT NULL,
typeID NUMBER(5),
CONSTRAINT seat_row_fk FOREIGN KEY("rowID") REFERENCES "row"("rowID"),
CONSTRAINT seatType_fk FOREIGN KEY(typeID) REFERENCES "seatType"("typeID"));

CREATE SEQUENCE seq_singularseat
START WITH 1000
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE
CACHE 5;

The major road blocks are that I have to use an arithmetic expression and cannot use a sub-query (or at least it did not work for me)

PS: since there is no restriction to which table this should have, you may try row as well. Seat seemed as a more likely choice for me.

An example of what I have tried:

ALTER TABLE seat
ADD total_seats_per_row NUMBER GENERATED ALWAYS AS
  (SELECT COUNT(*) FROM seat s WHERE s.rowID = seat.rowID) VIRTUAL;

I simplified your example (no foreign keys, removed double quotes (I just hate them).

SQL> create table seat
  2    (seat_id     number primary key,
  3     row_id      number,
  4     seat_number number
  5    );

Table created.

SQL> create table t_row
  2    (row_id     number primary key,
  3     row_number number
  4    );

Table created.

Sample data:

SQL> insert all
  2    into t_row values (1, 1)
  3    into t_row values (2, 2)
  4    into seat  values (1, 1, 3)         --> 4 seats in row 1
  5    into seat  values (2, 1, 4)
  6    into seat  values (3, 1, 8)
  7    into seat  values (4, 1, 11)
  8    into seat  values (5, 2, 6)         --> 1 seat in row 2
  9  select * From dual;

7 rows created.

Due to certain restrictions on virtual columns (can't reference columns from other tables), I'll create a deterministic function (must be; otherwise, that won't work):

SQL> create or replace function f_total_seats_per_row (par_row_id in number)
  2    return number deterministic
  3  is
  4    retval number;
  5  begin
  6    select count(*) into retval
  7      from seat
  8      where row_id = par_row_id;
  9    return retval;
 10  end;
 11  /

Function created.

Virtual column:

SQL> alter table t_row add
  2    (total_seats_per_row as (f_total_seats_per_row(row_id)));

Table altered.

Testing:

This is what an ordinary query returns:

SQL> select row_id, count(*) number_of_seats from seat group by row_id;

    ROW_ID NUMBER_OF_SEATS
---------- ---------------
         1               4
         2               1

What does table that contains virtual column return? The same data:

SQL> select * from t_row;

    ROW_ID ROW_NUMBER TOTAL_SEATS_PER_ROW
---------- ---------- -------------------
         1          1                   4
         2          2                   1

What if we add another seat into the 2nd row?

SQL> insert into seat values (6, 2, 1);

1 row created.

SQL> select * from t_row;

    ROW_ID ROW_NUMBER TOTAL_SEATS_PER_ROW
---------- ---------- -------------------
         1          1                   4
         2          2                   2
                                        ^ 
                                        |
                     here it is; 2 seats in the 2nd row

See if it helps.