query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-05-03 : 12:03:06

I have this query:

SELECT F_DIVISION_NO,
        Sub,
        Working,
        Declined,
        Lost,
        Quote,
        Bound,
        Cast(100 *((Declined * 1.0) / case Sub when 0 then 1 else Sub End)AS NUMERIC(8,2))  as Dec_Perc,
        Cast(100 * ((Quote * 1.0) / case Sub when 0 then 1 else Sub End) AS NUMERIC(8,2)) as Quote_Perc, 
        Cast(100*((Bound *1.0) / case Sub when 0 then 1 else Sub End) AS NUMERIC(8,2)) as Bind_Per_Sub,
        Cast(100*((Bound *1.0) / case Quote when 0 then 1 else Quote End) AS NUMERIC(8,2)) as Hit_Ratio
        
       
        
        
        From 
            (
            Select 
            DBO.THIT_RATIO_DETL.F_DIVISION_NO,
            count(dbo.THIT_RATIO_DETL.SUBMISSION_NO) as Sub,
            Sum(dbo.THIT_RATIO_DETL.WORKING_FL) as Working,
            Sum(dbo.THIT_RATIO_DETL.DECLINED_AUTO_FL) as  Declined,
            Sum(dbo.THIT_RATIO_DETL.LOST_FL) AS Lost,
            Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or               dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE,
            Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound
    
   
  
    
            
FROM  dbo.THIT_RATIO_DETL
Group by F_DIVISION_NO
    ) t

it works fine...

but when i put this line in

(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" Then sum(dbo.THIT_RATIO_DETL.PREMIUM_BOUND_AM) else NULL end) as Bound_GWP

I get an error that says: 'dbo.THIT_RATIO_DETL.STATUS_CD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I put status_CD in the group by clause and it throws the grouping off,I get multiple division numbers where i only want one division number. I don't understand in my other columns that i use the sum function i don't have to include them in my group by clause, any help is greatly appreciated....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 12:06:44

when you put it where? in the inner or the outer query?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 12:08:24

also try:
Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" Then dbo.THIT_RATIO_DETL.PREMIUM_BOUND_AM else 0 end) as Bound_GWP

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jpockets
Starting Member

45 Posts

Posted - 2007-05-03 : 12:11:57

Thanks for the reply!....

I put it in the inner...this is the query:

SELECT DISTINCT F_DIVISION_NO,
        Sub,
        Working,
        Declined,
        Lost,
        Quote,
        Bound,
        Cast(100 *((Declined * 1.0) / case Sub when 0 then 1 else Sub End)AS NUMERIC(8,2))  as Dec_Perc,
        Cast(100 * ((Quote * 1.0) / case Sub when 0 then 1 else Sub End) AS NUMERIC(8,2)) as Quote_Perc, 
        Cast(100*((Bound *1.0) / case Sub when 0 then 1 else Sub End) AS NUMERIC(8,2)) as Bind_Per_Sub,
        Cast(100*((Bound *1.0) / case Quote when 0 then 1 else Quote End) AS NUMERIC(8,2)) as Hit_Ratio,
   Bound_GWP
        
        
       
        
        
        From 
            (
            Select 
            DBO.THIT_RATIO_DETL.F_DIVISION_NO,
            count(dbo.THIT_RATIO_DETL.SUBMISSION_NO) as Sub,
            Sum(dbo.THIT_RATIO_DETL.WORKING_FL) as Working,
            Sum(dbo.THIT_RATIO_DETL.DECLINED_AUTO_FL) as  Declined,
            Sum(dbo.THIT_RATIO_DETL.LOST_FL) AS Lost,
            Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or               dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE,
            Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound,
            (Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" Then sum(dbo.THIT_RATIO_DETL.PREMIUM_BOUND_AM)  end) as Bound_GWP
    
   
  
    
            
FROM  dbo.THIT_RATIO_DETL
Group by F_DIVISION_NO
    ) t

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 12:15:04

have you tried putting the case inside the sum?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jpockets
Starting Member

45 Posts

Posted - 2007-05-03 : 12:21:25

Do you mean moving the sum outside the case:

Sum(CASE When dbo.THIT_RATIO_DETL.STATUS_CD ="B" Then dbo.THIT_RATIO_DETL.PREMIUM_BOUND_AM else 0 end) as Bound_GWP

Sorry but i'm a newbie, thank-you for the help

jpockets
Starting Member

45 Posts

Posted - 2007-05-03 : 12:22:10

It worked!!!!...Thank-you I'm getting close.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources