How to combine queries? 2

[jontout]

Hi Folks,

I have 2 queries that I'd like to combine but I'm having a rare old time figuring out the correct syntax.

Firstly the table layout - tblLossEvent.
The key fields are RiskCat which will only ever have 5 different enteries, ActLoss which is either 1 or 0 and LossValCurr which is the loss value associated with RiskCat.

Here are the 2 queries.

SELECT     RiskCat, SUM(LossValCur) AS ActLoss
FROM         dbo.tblLossEvent
WHERE     (LossType = 1)
GROUP BY RiskCat

and

SELECT     RiskCat, SUM(LossValCur) AS PotLoss
FROM         dbo.tblLossEvent
WHERE     (LossType = 0)
GROUP BY RiskCat

The format of the result should be RiskCat, ActLoss, PotLoss where 1 is an actual loss and 0 is potential.
I've had varying degrees of success, but not managed to correctly get the format that I need.
EG

RiskCat ActLoss PotLoss
ABCD 1234 5678
BCDE 2345 6789
...and so on.

Any ideas would be appreciated.

Thanks,

Jon

 

[gmmastros]

The difference between the 2 statements is the where clause, right?

Well, you can use a Case Statement to affect the sums, like this...

SELECT RiskCat, 
       SUM(Case When LossType = 0 Then LossValCur Else 0 End) AS PotLoss,
       SUM(Case When LossType = 1 Then LossValCur Else 0 End) As ActLoss
FROM   dbo.tblLossEvent
GROUP BY RiskCat

Notice that there is no where clause any more. If you have records where LossType is any value other than 1 or 0, those rows will show up in the results with 0's for PotLoss and ActLoss.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

would either one ofthose queries always have all the possible risk categories? If not is there someplace where you can get the possible categories. (I'm assuming inthis case you want to show zero if no records meet the criteria)

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

here's a possibility

select a.riskcat, coalesce(b.actloss, 0), coalesce(c.potloss, 0) from
(select distinct RiskCat FROM         dbo.tblLossEvent) a
left join 
(SELECT     RiskCat, SUM(LossValCur) AS ActLoss
FROM         dbo.tblLossEvent
WHERE     (LossType = 1)
GROUP BY RiskCat) b on a.riskcat = b.riskcat
left join
(SELECT     RiskCat, SUM(LossValCur) AS PotLoss
FROM         dbo.tblLossEvent
WHERE     (LossType = 0)
GROUP BY RiskCat) c on a.riskcat = c.riskcat

"NOTHING is more important in a database than integrity." ESquared

 

[jontout]

Thanks George & SQLSister for 2 prompt responses.
Due to the way I error trap my data collection, there will never be blanks so this is just great.
I'm more familiar with SUM(CASE) than Joins, but I didn't even think to consider summing the totals.