locked
Sum over few Product Categories RRS feed

  • Question

  • I am trying to calculate the Life Premium as given in the below table. I want to calculate the sum only for rows where the product category like 'Life%' but apply it across all the rows where we have an occurrence of a 'Life' Product for a policy, for every other Policy (i.e. the ones where there is no 'Life' product it should be 0)

    I tried the Sum(Cost) OVER (Product Sub Category) but it gives a value across all the Policies even the ones which don't have a Life Product.  Sadly the partition by clause doesn't allow a Where clause.

    Can someone please help?


    Tuesday, May 23, 2017 9:18 AM

Answers

  • Hi Uri,

    I tweaked the query a bit and seems to be working now.

    select #t.*,

           sum(case when prod like 'Life%' then price else 0 end) over

               (partition by policy) as lifepremium

    from #t;

    Just one of those days- i thought it will be super complex.

    Thank you for the continuous to and fros.

    Appreciate it.

    have a nice day.

    • Marked as answer by Nimish Rao Tuesday, May 23, 2017 9:17 PM
    Tuesday, May 23, 2017 9:17 PM

All replies

  • create table #t (prod varchar(100), price real)

    insert into #t values ('Life',120)
    insert into #t values ('Death',55)
    insert into #t values ('Prod',515)
    insert into #t values ('Life assurance',41)
    insert into #t values ('Life product',11)
    insert into #t values ('Funeral',15)


    with cte
    as
    (
    select prod, price,
    sum(case when prod like 'Life%' then price  end ) over ()    Lf from #t
    )  select prod,price,case when prod like 'Life%' then Lf else 0 end  lf
     from cte

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 23, 2017 9:37 AM
    Answerer
  • Thanks Uri.

    I think your answer is missing something I need it to repeat for all the Products which have even one Life product against the policy.

    So if you see my example above 1234-01 has two life products therefore life premium will have 1400 across all four products (1000+400 across the two life products) while '4568-09' will have 0 as the Life Premium as it has no Life Products.

     

    I ran your query and the above is what i got. 

    Tuesday, May 23, 2017 10:13 AM
  • How about it

    create table #t (policy  varchar(20),prod varchar(100), price real)

    insert into #t values ('1234','Life',120)
    insert into #t values ('1234','Death',55)
    insert into #t values ('5678','Prod',515)
    insert into #t values ('5678','Life assurance',41)
    insert into #t values ('5678','Life product',11)
    insert into #t values ('9777','Funeral',15)


    with cte
    as
    (
    select prod, price,
    sum(case when prod like 'Life%' then price  end ) over (partition by policy)    Lf from #t
    )  select prod,price,case when prod like 'Life%' then Lf else 0 end  lf
     from cte


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 23, 2017 10:39 AM
    Answerer
  • Thank you Uri, do you think we can get it to work without using a CTE? Additionally why are we using a CTE in this case- i thought CTE was for recursive queries.
    Tuesday, May 23, 2017 11:08 AM
  • Yes, we can

    select prod, price,
    case when prod like 'Life%' then sum(case when prod like 'Life%' then price  end ) over (partition by policy) else 0 end    Lf from #t


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Hilary CotterMVP Tuesday, May 23, 2017 11:30 AM
    • Unproposed as answer by Nimish Rao Tuesday, May 23, 2017 8:43 PM
    Tuesday, May 23, 2017 11:27 AM
    Answerer
  • Hi Uri,

    Sorry,I am probably not doing a good job of giving the correct explanation.

    I ran the code you gave me :

    create

    table#t(policy  varchar(20),prod varchar(100),price real)


    insertinto#t values ('1234','Life',120)


    insertinto#t values ('1234','Death',55)


    insertinto#t values ('5678','Prod',515)


    insertinto#t values ('5678','Life assurance',41)


    insertinto#t values ('5678','Life product',11)


    insertinto#t values ('9777','Funeral',15)


    go

    select

    Policy,prod,price,


    casewhenprod like'Life%'thensum(casewhenprod like'Life%'thenprice  end)over (partitionbypolicy)else0 end    Lf from#t

    I did add Policy to the select. The result I get is :

    I would expect both the 1234 rows to have 120 for Lf, rather than one having 120 and the other 0.Similarly 5678 should have 52 for each 'prod','Life Assurance' and 'Life product'.

    Hope it makes sense.

    Thanks,

    Nimish



    • Edited by Nimish Rao Tuesday, May 23, 2017 8:54 PM Image was not added
    Tuesday, May 23, 2017 8:52 PM
  • Hi Uri,

    I tweaked the query a bit and seems to be working now.

    select #t.*,

           sum(case when prod like 'Life%' then price else 0 end) over

               (partition by policy) as lifepremium

    from #t;

    Just one of those days- i thought it will be super complex.

    Thank you for the continuous to and fros.

    Appreciate it.

    have a nice day.

    • Marked as answer by Nimish Rao Tuesday, May 23, 2017 9:17 PM
    Tuesday, May 23, 2017 9:17 PM