Sam Carleton <scarle...@miltonstreet.com> wrote: > I have a select statement that is calculating the subTotal, Tax, and grand > total: > > UPDATE Invoice > SET Sub_Total = (select sum(PRICE * QTY) from INVOICE_ITEM where > INVOICE_ID = @invoiceId), > Tax = (select round( sum(PRICE * QTY) * ( @tax / 100) + .005, 2) from > INVOICE_ITEM where INVOICE_ID = @invoiceId), > Total = (select round( sum(PRICE * QTY) * ( 1 + ( @tax / 100)) + > .005, 2) from INVOICE_ITEM where INVOICE_ID = @invoiceId), > updatedby = @updatedby > WHERE Invoice_Id = @invoiceId; > > One thing I don't like is that each column contains the same basic inner > select. Can I optimize the query such that the Sub_Total is used in > calculating the Tax and Total:
Run two update statements. The first would calculate Sub_Total column, using the subselect. The second would calculate Tax and Total from Sub_Total. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
