SQL field as a sum of other fields

Question

This is not related to the query, I would like to know if the field in the column can be displayed as a sum of other fields. A bit like Excel.

As an example, I have two tables:

Recipes

nrecepie integer name varchar(255) time integer 

and the other

Instructions

 nintrucion integer nrecepie integer time integer 

So basically, as the recipe has n instructions, I would like

 recipes.time = sum(intructions.time) 

Is it possible to do this in the create script table? if so, how?

Answer 1

You can use the view:

 CREATE VIEW recipes_with_time AS SELECT nrecepie, name, SUM(Instructions.time) AS total_time FROM Recepies JOIN Instructions USING (nrecepie) GROUP BY Recepies.nrecepie 

If you really want to have this data in a real table, you should use a trigger.

Answer 2

This can be done using the INSERT/UPDATE/DELETE trigger. Each time the data changes in the Instructions table, the trigger starts and updates the value time in Recepies .

Answer 3

You can use a trigger to update the time column every time the command table changes, but the more “normal” (less redundant) way is to calculate the time column using the group by clause when connecting between instructions and the recipe table [sic].

Answer 4

Depends on the pn of the database provider ... For example, in SQL Server, you can create a column that calculates its value based on the values ​​of other columns on the same row. they are called computed columns, and you do it like this:

 Create Table MyTable ( colA Integer, colB Integer, colC Intgeer, SumABC As colA + colB + colC ) 

Typically, just enter the name of the column you want, the word “like,” and the formula or equation to enhance the meaning. This approach does not use aditonal storage, it calculates the value every time someone executes the selected aganist, so the profile of the table remains narrower and you get better performance. The only downsode is that you cannot put an index in a computed column. (although there is a flag on the SQL server that allows you to specify in the database that it should keep the value whenever it is created or updated ... In this case, it can be indexed)

In your example, however, you are accessing data from multiple rows in another table. To do this, you need a trigger, as suggested by other respondents.

Answer 5

In general, you want to avoid such situations because you store derivative information (there are exceptions for performance reasons). Therefore, the best solution is to create a view proposed by AndreKR. This always provides the correct amount, which is as easy to SELECT from the database as if it were in the actual, saved column.