INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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

How about something like this:

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close