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

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

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

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

Bye, Olaf.

## RE: Help with Sum

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

Bye, Olaf.

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

## RE: Help with Sum

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

## RE: Help with Sum

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

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

I'd say all you need is

## CODE

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