Virtual column referencing another table-CodePudding

I have a table with workday and peoples worked that day (M:N) and table workday with attribute = "Number of people worked that day".

I want to count in M:N table all rows with same workday id and to write that count in workday table as amount of people worked like generated column.

How do I do that?

I tried something like this...

alter table WORKDAY
add NUM_OF_EMPLOYEES NUMBER(3) GENERATED ALWAYS AS
  (COUNT(*) FROM WORKDAY_EMPLOYEES WHERE WORKDAY_EMPLOYEES.ID = THIS.ID)
  STORED NOT NULL;

CodePudding user response：

You can create a view.
You will need to substitute the names of columns and table.

CREATE VIEW number_employees_per_day AS
SELECT 
  date_column,
  COUNT(DISTINCT employee_id)
FROM table_name
GROUP BY date_column
ORDER BY date_column;

CodePudding user response：

It seems you don't know how to select the number of employees for a workday. Here is how (I had to make up the column id_workday, for there must be some columnlike this in the workday_employees to reference a workday):

SELECT
  w.*,
  (
    SELECT COUNT(*) 
    FROM workday_employees we 
    WHERE we.id_workday = w.id
  ) AS num_of_employees 
FROM workday w;

Another option:

SELECT
  w.*,
  COALESCE(w.total, 0) AS num_of_employees 
FROM workday w
LEFT JOIN
(
  SELECT id_workday, COUNT(*) AS total
  FROM workday_employees
  GROUP BY id_workday
) w ON w.id_workday = w.id;

Yet another option is a lateral join (OUTER APPLY).

Once you have the query, create a view for convenience:

CREATE VIEW workday_counted AS
  SELECT ... -- one of the queries above
  ;