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

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

Oleg
1#
Oleg Published in 2018-02-12 20:13:04Z

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.

JNevill
2#
JNevill Reply to 2018-02-12 20:45:05Z

Select your data, then aggregate it:

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,
    SUM(Quotes) as Quotes 
FROM (
    SELECT ProducerCode,
        ProducerLocationID,
        Producer,
        premium,
        PolicyType,
        ControlNo,
        QuotedPremium,
        STATUS,
        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'
    ) sub
GROUP BY ProducerCode,
    ProducerLocationID
Oleg
3#
Oleg Reply to 2018-02-12 21:43:53Z

Used subquery:

    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 THEN 1 ELSE 0 END ) as Rated,
--This is subquery--------------
            (
                SELECT COUNT( DISTINCT c.ControlNo) FROM [dbo].[tblClearanceDataForMetricReports] c INNER JOIN tblQuoteStatusChangeLog q ON c.ControlNo = q.ControlNo
                AND q.NewQuoteStatusID IN (2,25,202)
                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'
                and c.ProducerCode = [dbo].[tblClearanceDataForMetricReports].ProducerCode
                and c.ProducerLocationID = [dbo].[tblClearanceDataForMetricReports].ProducerLocationID
            ) as Quoted,
            SUM(CASE WHEN  Status = 'Declined' THEN 1 ELSE 0 END ) as  Declined
    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
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO