Subject Re: [firebird-support] how to add subtotal
Author Martijn Tonies

> I think that this is a fairly common job, but I don't know how to do it in
one sql.
> eg, in master/detail invoice tables
> table master has 2 fields (key1, tot)
> table detail has 3 fields (key1, linenum, subtot)
> I want to update all records in master so that
> each tot = sum(subtot) for master.key1=detail.key1

It depends, do you want to store the value or not?

If not, this could be a way:

alter table master
add total BIGINT COMPUTED BY ( (SELECT SUM(d.subtot) FROM detail d WHERE
d.key1= master.key1) )

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
My thoughts:
Database development questions? Check the forum!