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

# Help with Sum

## Help with Sum

(OP)
I have a very basic table as shown below

DOC_NUMBER - int
LAYER - int
PERCENTAGE - decimal(18,4)

There are '3' possible layers. 1, 2 or 3

I'm trying to get a total percentage for each layer by doc_number

so the records could look like this.

doc_number layer percentage
100 1 1.2
100 1 10.2
100 2 34.2
100 2 2.23
100 3 16.2

I'm looking at getting a sum for each layer in the same query. I can do this separate not a problem but was trying to avoid writing 3 separate stored procedures.

any help would be appreciated

Thanks

### RE: Help with Sum

Select doc_number, layer, SUM(percentage) As Sum_percentage
From SomeTable
Where layer = 1
Group By doc_number, layer
Union
Select doc_number, layer, SUM(percentage)
From SomeTable
Where layer = 2
Group By doc_number, layer
Union
Select doc_number, layer, SUM(percentage)
From SomeTable
Where layer = 3
Group By doc_number, layer

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

### RE: Help with Sum

(OP)
that's perfect, thank you.

now I need to be able to pass the sum percentages back to a web form. how can I make the sums parameters ?

### RE: Help with Sum

What do you want to make a parameter? The sum is computed, it can't be a parameter of the query, it is a result.

If you want to specify which layer sum you want, then you a) make the @layer int a parameter and b) could do with the simpler query:

#### CODE

Select doc_number, layer, SUM(percentage) As Sum_percentage
From SomeTable
Where layer = @layer
Group By doc_number 

Bye, Olaf.

### RE: Help with Sum

(OP)
okay, makes sense.

on my web form there is a data grid. that shows each layer and the percentage of each layer. now, on my web form I have three text boxes labled layer1, layer2, layer3 I need to pass the sum of each layer into those three text boxes. I'm just trying to avoid making three separate calls to the DB. that's why I'm trying to get those results into one query.

Thanks

### RE: Help with Sum

Well, then you just need the one query. No parameter. You split the result at the website level, eg JS.

Bye, Olaf.

### RE: Help with Sum

(OP)
how can I insert the following into a temp table grab the results and drop the temp table ?

Select doc_number, layer, SUM(percentage) As Sum_percentage
From SomeTable
Where layer = 1
Group By doc_number, layer
Union
Select doc_number, layer, SUM(percentage)
From SomeTable
Where layer = 2
Group By doc_number, layer
Union
Select doc_number, layer, SUM(percentage)
From SomeTable
Where layer = 3
Group By doc_number, layer

### RE: Help with Sum

You don't need a temp table, you make that query as adhoc sql query, it returns it's data and doesn't leave any footprint in the database.
And if you would creater a temp table you would create it temporary, that's the nature of temp tables, they remove themselves.

Bye, Olaf.

### RE: Help with Sum

(OP)
I need to pass the percentages to a web form. I need some way to grab those values and pass them to text boxes on a web form.

### RE: Help with Sum

(OP)
for those interested I ended up doing this. seems to work fine. so far...

CREATE PROCEDURE MY_SP

@DOC_NUMBER INT

AS

SELECT SUM(CASE
LAYER WHEN 1 THEN PERCENTAGE END) AS total1
,SUM(CASE
LAYER WHEN 2 THEN PERCENTAGE END) AS total2
,SUM(CASE
LAYER WHEN 3 THEN PERCENTAGE END) AS total3

FROM

MY_TABLE

WHERE DOC_NUMBER = @DOC_NUMBER

### RE: Help with Sum

When you can call a stored procedure, you can also execute a whole sql query.

Anyway, you've actually made a better query than the unions.

I'd say all you need is

#### CODE

Select layer, SUM(percentage) As Sum_percentage From SomeTable WHERE DOC_Number = X Group By layer

From what you initially said you wanted to filter by layer and not doc_number. Anyway. No need to create a stored proc to execute some query.

Bye, Olaf.

### RE: Help with Sum

(OP)
Thanks for the info. I just try to avoid writing actual sql statements in my form code. it's best for performance to call stored procedures as much as possible. but again, thank you for your info. appreciate it.

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