How to use count distinct

When I do count(distinct) it is saying that is not a valid formula.  Below is my formula.  I am counting the sales names named (SalesHost), but against their sales and I want to sum the sales. I added a beastmode to calculate the rank, but need to get rid of the duplicate sales names.  The first is invalid, but the second is not and the second wont allow aggregation.

 

count(distinct `SalesHost`) over (order by `NetSales` desc)   

 

count(`SalesHost`) over (order by `NetSales` desc)

Best Answers

  • ST_-Superman-_
    Accepted Answer

    I answered this in another chain...

    @user14340 - Can you provide some sample data so that I can test it out with your schema?

     

    I'm not sure what is causing the issue.  I might try

    count(`SalesHost`) over (order by sum(`NesSales`) desc)

    but i'm not sure if that is valid or not.  

     

    @user14340 also commented that the rank() function worked better for what he was wanting to do

  • DataMaven
    DataMaven 🟢
    Accepted Answer
    This may seem crazy, but try adding a SUM. I've had that do the trick on beast modes not working.

    SUM(count(distinct `SalesHost`)) over (order by `NetSales` desc)

Answers