Dividing in SQL

I currently have this code: categorizing some leads.

Select

    `Year-Month` 

,`Lead Source`

   ,`Product Line`

   ,`Product Models`

   ,`Country`

   ,`Early Stage No Movement`

   ,`Stage 0 to Early`

   ,`Early to Lost`

   ,`Early to Won`

   ,`Early to Late`

   ,`Late to Lost`

   ,`Late to Won`

,Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total Low to High'

,SUM(`Early Stage No Movement`+`Stage 0 to Early`+`Early to Lost`+`Early to Won`+`Early to Late`+`Late to Lost`+`Late to Won`) AS 'Total'

FROM `aggregation_categorization`


GROUP BY

`Year-Month`

,`Lead Source`

,`Product Line`

,`Product Models`

,Country

This runs fine, the next thing I am trying to do is add in the %.

SELECT *

,`Total Low to High`/`Total` AS '% Of Low to High'

FROM `formulas`

This works fine as well, but when I place it in a sumo table aggregating it does work. Any suggestions would be awesome.

Thank you.

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    Given the math you've put on the screen, there is no reason to run this ETL as a SQL transform. Just keep your data unaggregated.


    Be careful with

    Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total Low to High'
    

    for any row, if any column contains the value null, the entire result will be null. this is the appropriate adjustment.

    Sum(ifnull(`Early to Lost`,0) +ifnull(`Early to Won`,0)+ifnull(`Early to Late`,0) ) AS 'Total Low to High'
    
    

    to do the division you're describing, just calculate the ratio in a beast mode (you may have to build the beast mode in analyzer, then share it to the dataset in order to reference it in a sumo card.


    Personally i recommend you don't use the Sumo card and just use the Pivot Table card in Analyzer.

Answers

  • GrantSmith
    GrantSmith Indiana 🟤

    Hi @gbennett

    Did you mean to say aggregating it doesn’t work? How does it currently work and how are you expecting it to work?

  • gbennett
    gbennett ⚪️

    Hey Grant,

    I would think, that if I pulled the data in a sumo card, I could group specifically on a year-month and product line and it would provide the % of Low to High. But rather it adds the % of Low to High for all records. I hope that makes sense.

    I appreciate your help.

    Thanks.

  • I was able to use Sum(ifnull(`Early to Lost`,0) +ifnull(`Early to Won`,0)+ifnull(`Early to Late`,0) ) AS 'Total Low to High'
    

    and enter in a beast mode.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!