Home How chould I rewrite query when: Cannot perform an aggregate function on an expression containing an aggregate or a subquery
Reply: 0

How chould I rewrite query when: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

user2729
1#
user2729 Published in May 28, 2018, 3:18 am

How should I rewrite below query to get desirable outcome? The last SUM gives me trouble.

   SELECT  ProducerCode,ProducerLocationID, 
                    MAX(Producer) as Producer, -- using MAX() to  combine same ProdCode and ProdLocation BUT diff ProdNames
                    SUM(premium) as NetWrittenPremium,
                    SUM(CASE WHEN PolicyType = 'New Business' THEN Premium ELSE 0 END) as WPNewBusiness,
                    SUM(CASE WHEN PolicyType = 'Renewal' THEN Premium ELSE 0 END) as WPRenewal,
                    SUM(CASE WHEN PolicyType = 'Rewrite' THEN Premium ELSE 0 END) as WPRewrite, 
                    SUM(CASE WHEN PolicyType = 'New Business' AND Status = 'Bound' THEN 1 ELSE 0 END) as BindsNewBusiness,
                    SUM(CASE WHEN PolicyType = 'Renewal' AND Status = 'Bound' THEN 1 ELSE 0 END) as BindsRenewals,
                    SUM(CASE WHEN PolicyType = 'Rewrite' AND Status = 'Bound' THEN 1 ELSE 0 END) as BindsRewrite,
                    SUM(CASE WHEN PolicyType = 'New Business' AND Status = 'Bound' THEN 1 ELSE 0 END) + SUM(CASE WHEN PolicyType = 'Renewal' AND Status = 'Bound' THEN 1 ELSE 0 END) + SUM(CASE WHEN PolicyType = 'Rewrite' AND Status = 'Bound' THEN 1 ELSE 0 END) as PolicyCount, 
                    COUNT (distinct ControlNo) as Submissions,
                    SUM(CASE WHEN QuotedPremium IS NOT NULL AND Status <> 'Quoted' THEN 1 ELSE 0 END ) as Rated,
                    SUM(CASE WHEN QuotedPremium IS NOT NULL AND Status = 'Quoted' THEN 1 ELSE 0 END) as Quoted,

                --This is where it gets tricky---
---------------------------------------------------------------------------
                    SUM(    CASE WHEN EXISTS (SELECT * 
                                  FROM tblQuoteStatusChangeLog 
                                  WHERE [dbo].[tblClearanceDataForMetricReports].ControlNo = tblQuoteStatusChangeLog.ControlNo 
                                        AND tblQuoteStatusChangeLog.NewQuoteStatusID IN (2,25,202)
                                  ) THEN 1 ELSE 0 END) as Quotes
   ----------------------------------------------------------------------------
            FROM    [dbo].[tblClearanceDataForMetricReports]
            WHERE   CAST(EffectiveDate AS DATE) >= DateAdd(yy, -1, DATEADD(d, 1, EOMONTH(GETDATE()))) AND CAST(EffectiveDate AS DATE) <= EOMONTH(GETDATE())      
                    AND CompanyLocationGUID = '54A8FCCD-C7FE-4642-9C22-3A25207CDAEE'
                    AND LineGUID = '43280452-42E9-4D4C-9B72-C51DCF77BCD0'  
            GROUP BY ProducerCode,
                    ProducerLocationID

I tried to make it as a derived table, but also no success.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.342242 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO