getting the sum

Question

Post reply

getting the sum

cpinky01

Ten Centuries

Points: 1084
More actions
August 28, 2010 at 7:19 am

#228382

Hi,
I have sales type and under which i have
sales for diff products
and Calims under which i have different types of claims
like this:
1.Sales
a. Product 1
b. Product 2
c. Product 3
2. Claims
a. Claim1
b. Claim2
I need to find find
the sum of all the sales for product 1,
the sum of all the sales for product 2,
the sum of all the sales for product 3,
the sum of all the sales for Claims1,
the sum of all the sales for Claims3,
also in other column i want
sum of (product1,product2,product3) under total sum of sales
and
in other column
i want sum of (claim1, claim2) under total sum of claims.
i want to implement every thing one single stored proc. How can we do it?
This is how the result set has to be
Overall_T-------------sum(sales+cliams)
sales_T ------------- total sales this include product1+product2+product3
product1_T ---------Product1 total
product1_T ---------Product2 total
product1_T ---------Product1 total
Claims_T ------------- total Claims this include Claims1+Claims2
claims1_T ---------Claims1 total
Calims1_T ---------Calimst2 total
Thanks
Preethi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 1

Hi Preethi

Please could you post the DDL of the tables involved?

MCITP SQL Server 2005/2008 DBA/DBD

cpinky01 Ten Centuries Points: 1084 More actions · Answer 2

Hi,

It is some thing like this.

In one column "type"

in the sother column "detailed type"

there is one more column where in the value is available.

in type i have sales and claims

in detail type i have product1, product2,product3,claims1,cliams2

Table is something like this

type -- detailed type-- Total

-----------------------------

sales---Produt1--------2

sales---Product2-------4

sales---product3-------7

sales---Produt3--------2

sales---Product2-------4

sales---product1-------7

Claims--Claim1---------8

Claims--Claim2---------6

Claims--Claim1---------8

Claims--Claim2---------7

and so on

i need to find the

sum of products1 which comes under type Sales

sum of products2 which comes under type Sales

sum of products3 which comes under type Sales

sum of ckaims1 which comes under type Calims

sum of ckaims2 which comes under type Calims

scenario is something like.

Thanks

Preethi

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

cpinky01 (8/28/2010)
I have sales type and under which i have
sales for diff products
and Calims under which i have different types of claims
like this:
1.Sales
a. Product 1
b. Product 2
c. Product 3
2. Claims
a. Claim1
b. Claim2
I need to find find
the sum of all the sales for product 1,
the sum of all the sales for product 2,
the sum of all the sales for product 3,
the sum of all the sales for Claims1,
the sum of all the sales for Claims3,
also in other column i want
sum of (product1,product2,product3) under total sum of sales
and
in other column
i want sum of (claim1, claim2) under total sum of claims.
i want to implement every thing one single stored proc. How can we do it?
This is how the result set has to be
Overall_T-------------sum(sales+cliams)
sales_T ------------- total sales this include product1+product2+product3
product1_T ---------Product1 total
product1_T ---------Product2 total
product1_T ---------Product1 total
Claims_T ------------- total Claims this include Claims1+Claims2
claims1_T ---------Claims1 total
Calims1_T ---------Calimst2 total

Is this homework? Try group by.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

PaulB-TheOneAndOnly (8/28/2010)
cpinky01 (8/28/2010)
I have sales type and under which i have
sales for diff products
and Calims under which i have different types of claims
like this:
1.Sales
a. Product 1
b. Product 2
c. Product 3
2. Claims
a. Claim1
b. Claim2
I need to find find
the sum of all the sales for product 1,
the sum of all the sales for product 2,
the sum of all the sales for product 3,
the sum of all the sales for Claims1,
the sum of all the sales for Claims3,
also in other column i want
sum of (product1,product2,product3) under total sum of sales
and
in other column
i want sum of (claim1, claim2) under total sum of claims.
i want to implement every thing one single stored proc. How can we do it?
This is how the result set has to be
Overall_T-------------sum(sales+cliams)
sales_T ------------- total sales this include product1+product2+product3
product1_T ---------Product1 total
product1_T ---------Product2 total
product1_T ---------Product1 total
Claims_T ------------- total Claims this include Claims1+Claims2
claims1_T ---------Claims1 total
Calims1_T ---------Calimst2 total
Is this homework? Try group by.

Heh... I was going to suggest paying attention in class for a change. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions