Solved

# SQL - ROLLUP Data

Posted on 2012-09-16
Medium Priority
284 Views
I would like to generate the following result,
For PlanCode = P1
RiskLabel               Amount              Risk
---------------              -------------              -------
Low                          10                       1
Medum                     0                        2
High                          0                        3

For PlanCode = P2
RiskLabel               Amount              Risk
---------------              -------------              -------
Low                           0                       1
Medum                    20                      2
High                         70                      3

I am not sure how to do that based the following data I have
Can anyone help me on this?

IF OBJECT_ID('tempdb..#investment') IS NOT NULL
DROP TABLE #investment;

IF OBJECT_ID('tempdb..#Fund') IS NOT NULL
DROP TABLE #Fund;

IF OBJECT_ID('tempdb..#Risk') IS NOT NULL
DROP TABLE #Risk;

CREATE TABLE #investment
(
InvestmentCode varchar(50) NOT NULL,
PlanCode varchar(50) NOT NULL,
FundCode varchar(50) NOT NULL,
Amount int NOT NULL
)
go

INSERT INTO #investment
VALUES
('123', 'P1', 'F1', 10),
('456', 'P2', 'F2', 20),
('789', 'P2', 'F3', 30),
('321', 'P2', 'F3', 40)

GO

CREATE TABLE #Fund
(
FundCode varchar(50) NOT NULL,
Risk int NOT NULL
)
go

INSERT INTO #Fund
VALUES
('F1', 1),
('F2', 2),
('F3', 3)

GO

CREATE TABLE #Risk
(
RiskLabel varchar(50) NOT NULL,
Risk int NOT NULL
)
go

INSERT INTO #Risk
VALUES
('Low', 1),
('Medium', 2),
('High', 3)

GO
Select a.InvestmentCode, a.PlanCode, a.FundCode,  b.Risk,  SUM(a.Amount) As Total from #investment a
inner join #Fund b on a.FundCode = b.FundCode
Group By b.Risk, a.InvestmentCode, a.PlanCode, a.FundCode  with ROLLUP
0
Question by:tommym121
• 3
• 3

LVL 25

Expert Comment

ID: 38404182
What is the relationship between 'risk' and the amount?
0

Author Comment

ID: 38404220
I basically trying to get the risk analysis of my investment within each investment plan (say P1 and P2_.
I like to know what is my total amount of investment for each plan in each risk category.
For Plan P2,  my investment is mainly in two seperate category (2 and 3).  The category 2 have only 1 investment, it totals to 20, Category 3 have two investment, it totals up to 70.

Let me know if that make sense.

I like to create a stored procedure given the Plan code, I will provide the risk analysis of the investment within the plan
0

LVL 25

Expert Comment

ID: 38404226
But what makes something low, medium risk or high risk?
0

Author Comment

ID: 38404289
The Low, Medium, and High Risk is defined in the #Risk table

A Plan have one or  more investment
Each investment is one of the fund in #fund
Each fund is associated with certain risk defined in the Risk field '1, 2 or 3'
Label of the Risk is defined in the #Risk table

Hope that makes sense.
0

LVL 25

Accepted Solution

ID: 38404332
try:
``````;with fund_data as (
Select a.PlanCode, b.Risk,  SUM(a.Amount) As Total
from #investment a
inner join #Fund b on a.FundCode = b.FundCode
where a.PlanCode = 'P2'
Group By b.Risk, a.PlanCode
)
select RiskLabel, isnull(Total,0) as Amount, r.Risk
from #risk r
left join fund_data f on r.risk = f.risk
``````
0

Author Closing Comment

ID: 38404368
Thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
###### Suggested Courses
Course of the Month16 days, 15 hours left to enroll