IzzyTwinkly
asked on
The use of Over()
Hello,
I have a question about the following query.
I understand most parts of the following query, but I don't understand ' * 10000/count(*) Over() ' part.
Why would we multiply '1000/Count(*)'? and what does Over() do at the end of the query?
Select SalesPersonID, Sum(TotalDue) as TotalSales,
NTILe(10) Over (Order by Sum(TotalDue)) * 10000/Count(*) Over() as Bonus
from Sales.SalesOrderHeader
where SalesPersonID is not null
group by SalesPersonID
order by TotalSales;
I have a question about the following query.
I understand most parts of the following query, but I don't understand ' * 10000/count(*) Over() ' part.
Why would we multiply '1000/Count(*)'? and what does Over() do at the end of the query?
Select SalesPersonID, Sum(TotalDue) as TotalSales,
NTILe(10) Over (Order by Sum(TotalDue)) * 10000/Count(*) Over() as Bonus
from Sales.SalesOrderHeader
where SalesPersonID is not null
group by SalesPersonID
order by TotalSales;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IzzyTwinkly, do you still need help with this question?
Hi Izzy, glad that helped.
ASKER
Thanks dsacker!
It was a really big help!
It was a really big help!
The way the code does it is interesting and somewhat counter-intuitive, just because we are accustomed to seeing "top sales" reports from hi to low rather than low to hi.
NTILE(10) will always return a number between 1 and 10. That number then gets multiplied by 10,000, and finally divided by the total number of groups. Since sales are sorted low to high, higher sales will have higher NTILE(10) values and thus get higher bonuses, but the bonus can never exceed 10,000 per SalesPersonId (per row).