Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Using COUNT(DISTINCT.. inside SUM(CASE..

    Hi All

    This ones got me scratching my feeble head (SQL 2000)

    Essentially from the query below I would like column [f] to reflect the same calculation as column [c] but incorporating the sub filter

    This is an AdHoc so performance not a problem!

    I cannot calculate the DISTINCT Policy Numbers because I'm using the SUM(CASE WHEN...... Construct

    I'm hoping someone can just point me in the right direction

    Maybe I should get rid of the SUM(CASE WHEN's... , but what would be best to use instead?

    Hope this makes sense, Any help much appreciated

    Code:
     
    SELECT         
    BLiveSections.CoverClass --[a]  
    ,COUNT (DISTINCT BLiveSections.Section_reference) 
    AS CovClassMapsToThisNoOfSections --[b]  
    ,COUNT (DISTINCT BLivePolicys.Policy_No) 
    AS PolicysContainingThisCovCLass -- [c]********  
    ,CONVERT(INT,SUM(1 / SectionCount)) 
    AS PolicyCount_Calc -- [d]  
    ,COUNT(*) 
    AS Covers_LegacySections  -- [e] 
    ,CONVERT(INT,SUM(CASE WHEN (BLivePolicys.BRANCH_NO IN ('02', '07'))    
       AND (MONTH(BLivePolicys.NEXT_RENL_DATE) = 8) THEN 1 ELSE 0 END)) 
    AS AugNE_PolicysContainingThisCovCLass --[f] *****
    ,CONVERT(INT,SUM(CASE WHEN (BLivePolicys.BRANCH_NO IN ('02', '07'))    
       AND (MONTH(BLivePolicys.NEXT_RENL_DATE) = 8) THEN 1 / SectionCount ELSE 0 END)) 
    AS AugNE_PolicyCount_Calc --[g]
    ,CONVERT(INT,SUM(CASE WHEN (BLivePolicys.BRANCH_NO IN ('02', '07'))    
       AND (MONTH(BLivePolicys.NEXT_RENL_DATE) = 8) THEN 1 ELSE 0 END)) 
    AS AugNE_Covers_LegacySections --[h] 
    FROM  BLivePolicys INNER JOIN BLiveSections ON 
                 BLivePolicys.Policy_No = BLiveSections.POLICY_NO   
    GROUP BY BLiveSections.CoverClass
    Code to generate tables for above query
    Code:
     
    CREATE TABLE BLiveSections (
     POLICY_NO varchar (12) NOT NULL ,
     BusPolicy varchar (15) NULL ,
     SECTION_REFERENCE varchar (5) NOT NULL ,
     SECTION_NO int NOT NULL ,
     CoverClass varchar (20) NULL 
    ) 
    CREATE TABLE BLivePolicys (
     Policy_No char (12) NOT NULL ,
     BusPolicy varchar (15) NULL ,
     product_code char (4) NOT NULL ,
     BRANCH_NO char (2) NOT NULL ,
     NEXT_RENL_DATE varchar (20) NOT NULL ,
     CoreOrPack varchar (15) NULL ,
     Tranche int NULL ,
     SectionCount decimal(18, 0) NULL 
    )
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ... , Count(DISTINCT CASE WHEN blah = blah THEN BLivePolicys.Policy_No END) As meh
    any good?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Works a Treat

    Thanks Georgev
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, you do ~not~ need to CONVERT a SUM to INT, and you won't need to convert a count, either

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •