CODEWITHSUNDEEP
sqlsql-servercorrelated-subquery
SqlBrain
SqlBrain

Reputation: 43

How to get rid of these co-related sub queries from select statement

Here I am dealing with only one table, It has an attribute called CmpltStscd where for each value I need a different column in the output as an aggregate function.

Is there a way to get rid of these subqueries ?

Trying a lot

Select 
      Mg.RsrcId
    , Count(Mg.ActID) Num_of_Goals
    , (SELECT COUNT(MC.ActID) FROM TM.MatrixGoal MC where MC.CmpltStsCd = 2 AND MG.RsrcID = MC.RsrcID AND MC.ActiveFlg = 1 AND MC.DelFlg = 0 AND MC.CorporateGoalFlg <> 1 AND MC.StsCd in (3,4)) as Complete
    , (SELECT COUNT(MI.ActID) FROM TM.MatrixGoal MI where MI.CmpltStsCd = 4 AND MG.RsrcID = MI.RsrcID AND MI.ActiveFlg = 1 AND MI.DelFlg = 0 AND MI.CorporateGoalFlg <> 1 AND MI.StsCd in (3,4)) as Issues
    , (SELECT COUNT(MO.ActID) FROM TM.MatrixGoal MO where MO.CmpltStsCd = 1 AND MG.RsrcID = MO.RsrcID AND MO.ActiveFlg = 1 AND MO.DelFlg = 0 AND MO.CorporateGoalFlg <> 1 AND MO.StsCd in (3,4)) as OnTrack
From 
TM.MatrixGoal AS Mg

Where MG.ActiveFlg = 1
            AND MG.DelFlg = 0
            AND MG.CorporateGoalFlg <> 1
            AND MG.StsCd in (3,4)
Group By RsrcId

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I think you can just use conditional aggregation:

Select Mg.RsrcId, Count(Mg.ActID) Num_of_Goals,
       SUM(CASE WHEN mg.CmpltStsCd = 2 THEN 1 ELSE 0 END) as Complete,
       SUM(CASE WHEN mg.CmpltStsCd = 4 THEN 1 ELSE 0 END) as Issues
       SUM(CASE WHEN mg.CmpltStsCd = 1 THEN 1 ELSE 0 END) as OnTrack
From TM.MatrixGoal Mg
Where MG.ActiveFlg = 1 AND MG.DelFlg = 0 AND MG.CorporateGoalFlg <> 1 AND 
     MG.StsCd in (3,4)
Group By RsrcId;

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453990

You need a pivot/cross tab query

SELECT Mg.RsrcId,
       COUNT(Mg.ActID) Num_of_Goals,
       SUM(CASE
             WHEN CmpltStsCd = 2 THEN 1
             ELSE 0
           END)        AS Complete,
       SUM(CASE
             WHEN CmpltStsCd = 4 THEN 1
             ELSE 0
           END)        AS Issues,
       SUM(CASE
             WHEN CmpltStsCd = 1 THEN 1
             ELSE 0
           END)        AS OnTrack
FROM   TM.MatrixGoal AS Mg
WHERE  MG.ActiveFlg = 1
       AND MG.DelFlg = 0
       AND MG.CorporateGoalFlg <> 1
       AND MG.StsCd IN ( 3, 4 )
GROUP  BY RsrcId

Upvotes: 3

Related Questions