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)

Find more posts tagged with

Accepted answers

ST_Superman

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

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)

All comments

ST_Superman

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

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)

user14340

@DataMaven , @ST_-Superman-_ Both of these worked, thank you!

DataMaven

@DaniBoy - Superman and I have another dual solution - would you mind adding a solution for him, too? Thanks!

[Deleted User]

Great collaboration @DataMaven and @ST_-Superman-_

As far as I can tell all of the suggestions you both provided in the thread are marked as solutions. DId I miss something?

DataMaven

They weren't earlier - thought that was something you had to do, and wanted to make sure that @ST_-Superman-_ got his due! ?

[Deleted User]

Kudos to @user14340 who has the power to mark the solution as the original poster.

You can update your user profile (User Name and Avatar) here.

Thanks!