SQL field as sum of other fields

Question

This is not query related, what I would like to know is if it's possible to have a field in a column being displayed as a sum of other fields. A bit like Excel does.

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 a recipe has n instructions I would like that

recipes.time = sum(intructions.time)

Is this possible to be done in create table script?? if so, how?

Answer 1

You can use a 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 that data in the real table, you must use a trigger.

Answer 2

This could be done with an INSERT/UPDATE/DELETE trigger. Every time data is changed in table Instructions, the trigger would run and update the time value in Recepies.

Answer 3

In general, you want to avoid situations like that because you're storing derived information (there are exceptions for performance reasons). Therefore, the best solution is to create a view as suggested by AndreKR. This provides an always-correct total that is as easy to SELECT from the database as if it were in an actual, stored column.

Answer 4

Depends pn the database vendor... In SQL Server for example, you can create a column that calculates it's value based on the values of other columns in the same row. they are called calculated columns, and you do it like this:

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

In general just put the column name you want, the word 'as' and the formula or equation to ghenerate the value. This approach uses no aditonal storage, it calculates thevalue each time someone executes a select aganist it, so the table profile remains narrower, and you get better performance. The only downsode is you cannot put an index on a calculated column. (although there is a flag in SQL server that allows you to specify to the database that it should persist the value whenever it is created or updated... In which 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 respondants.

Answer 5

You can use a trigger to update the time column everytime the instructions table is changed, but a more "normal" (less redundant) way would be to compute the time column via a group by clause on a join between the instructions and recepies [sic] table.