• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

SQL - ROLLUP Data

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
tommym121
Asked:
tommym121
  • 3
  • 3
1 Solution
 
lwadwellCommented:
What is the relationship between 'risk' and the amount?
0
 
tommym121Author Commented:
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
 
lwadwellCommented:
But what makes something low, medium risk or high risk?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
tommym121Author Commented:
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
 
lwadwellCommented:
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

Open in new window

0
 
tommym121Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now