Help with Sum

[dvannoy]

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

 

[Andrzejek]

How about something like this:
[tt]
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[/tt]

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.

 

[dvannoy]

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 ?

 

[Olaf Doschke]

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:

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

Bye, Olaf.

 

[dvannoy]

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

 

[Olaf Doschke]

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

Bye, Olaf.

 

[dvannoy]

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

 

[Olaf Doschke]

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.

 

[dvannoy]

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.

 

[dvannoy]

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

 

[Olaf Doschke]

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

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.

 

[dvannoy]

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.