0

I created a totals table which is a summation of column of another table but when i add another entry in the parent table the totals table is not changing below is my sql quey

\\
CREATE TABLE emphayatotals  AS

SELECT 

SUM(available_for_use_month)
,SUM(available_for_use_today)
,SUM(expected_tomorrow)
,SUM(expected_before_monthend)

FROM emphayas;

I tried creating the table ussing As but still not changing the totals in the total table even if i refresh

user17613159
  • 11
  • 2
  • 2
    'parent table' ? I think what you want is a view. Logically the only way to do what you're describing is with a view, or using triggers on the source table(s). – AD7six Nov 15 '22 at 10:10
  • 1
    Does this answer your question? [What are views good for?](https://stackoverflow.com/questions/214132/what-are-views-good-for) – AD7six Nov 15 '22 at 10:13
  • Think about it! Once you do this and create a new table, within seconds it is likely to be Out of Date as something will have changed on `emphayas` – RiggsFolly Nov 15 '22 at 10:24
  • @RiggsFolly so you saying there is nothing wrong with my code – user17613159 Nov 15 '22 at 10:30
  • NO! I am saying DONT DO THAT!! Do as @AD7six suggested and create a VIEW. Then when you `select from viewname` you will get the correct results EVERY TIME you look – RiggsFolly Nov 15 '22 at 10:34
  • 1
    A create..select is a one off event..ie the created table cannot 'see' changes in the parent nor can you define columns in a table which access another table. Generally speaking unless there are performance issues it's better to calculate on demand rather than store. This is why you are being steered towards a view..having said that a view may not be appropriate for you but without more detail I'm not seeing a viable alternative. – P.Salmon Nov 15 '22 at 10:49

0 Answers0