## Select / calculate multple rows then update single row

## Select / calculate multple rows then update single row

(OP)

Hi,

Looking for help with a bit of a complex query that is going over my head.

In my sample data there are two salesorderid’s, 1 and 2, which both have products associated to them, order 1 has 2 products and order 2 has 3 products. Each sales order also has a product row ‘card fee’ with a null total. I need to do a calculation per salesorderid which is SUM(total) for all products * (0.02) and then set the total column with the result of this calculation. Of course there could be thousands of salesorderid’s in this scenario each with varying products so it needs to be dynamically done.

So I need help with the update query and the nested select that does the maths. I'm not sure really where to start with this one. Any ideas would be much appreciated.

Thanks for looking.

Looking for help with a bit of a complex query that is going over my head.

In my sample data there are two salesorderid’s, 1 and 2, which both have products associated to them, order 1 has 2 products and order 2 has 3 products. Each sales order also has a product row ‘card fee’ with a null total. I need to do a calculation per salesorderid which is SUM(total) for all products * (0.02) and then set the total column with the result of this calculation. Of course there could be thousands of salesorderid’s in this scenario each with varying products so it needs to be dynamically done.

So I need help with the update query and the nested select that does the maths. I'm not sure really where to start with this one. Any ideas would be much appreciated.

Thanks for looking.

#### CODE

CREATE TABLE [dbo].[test]( [SalesOrderID] [int] NULL, [Qty] [int] NULL, [Price] [numeric](18, 2) NULL, [Total] [numeric](18, 2) NULL, [Product_Code] [varchar](75) NULL, ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO -- delete from test insert into test (salesorderid, qty, price, total, product_code) values (1,1,'100','100','prod 1') insert into test (salesorderid, qty, price, total, product_code) values (1,2,'100','200','prod 2') insert into test (salesorderid, qty, price, total, product_code) values (1,1,'0','0','card fee') insert into test (salesorderid, qty, price, total, product_code) values (2,1,'30','30','prod 1') insert into test (salesorderid, qty, price, total, product_code) values (2,2,'30','60','prod 2') insert into test (salesorderid, qty, price, total, product_code) values (2,3,'28.50','85.50','prod 3') insert into test (salesorderid, qty, price, total, product_code) values (2,1,'0','0','card fee') /****** Script for SelectTopNRows command from SSMS ******/ SELECT [SalesOrderID] ,[Qty] ,[Price] ,[Total] ,[Product_Code] FROM [wce_site_test].[dbo].[test] -- Sum to work out card fee for sales order 1 SELECT SUM(total) * (0.02) AS creditcard_fee FROM test where (salesorderid ='1' and not Product_Code = 'card fee') -- Sum to work out card fee for sales order 2 SELECT SUM(total) * (0.02) AS creditcard_fee FROM test where (salesorderid ='2' and not Product_Code = 'card fee')

## RE: Select / calculate multple rows then update single row

## CODE

Tamar