INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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.

#### 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

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

If I understand what you're asking for, this should be close:

#### CODE

WITH Fee (SalesOrderID, creditcard_fee) AS
(SELECT SalesOrderID, SUM(total) * 0.02
FROM Test
WHERE Product_Code <> 'card fee'
GROUP BY SalesOrderID)

UPDATE Test
SET Total = creditcard_fee
WHERE Test.SalesOrderID = Fee.SalesOrderID
AND Test.Product_Code = 'card fee' 

Tamar

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!