CODEWITHSUNDEEP
sqlsql-serverexcelfrequency
Username Not Exist
Username Not Exist

Reputation: 523

sql frequency function just like Excel Frequency

Hi So as you may know there is a Frequency function in Excel like:

=FREQUENCY(G3:G693,M683:M693)

Which will regroup column data into new group and count the frequency.

So how can I do the exact same thing in SQL Server in Stored Procedure???

Thank you so much for your reply...

Upvotes: 1

Views: 747

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You would do this using a join and careful aggregation. Call the two tables Scores and Bins. The following is a close approximation:

select b.bin, count(s.score)
from Bins b left outer join
     Scores s
     on s.score <= b.bin
group by b.bin;

The one thing it doesn't have is the final bin for the "greater than everything else" group. However, by choosing the bin limits well, this probably won't be necessary. And, if you need that functionality, replace the left outer join with a full outer join.

EDIT:

The above is cumulative frequency. Oops. Here is the correct frequency calculation:

select b.bin, b.nextbin, count(s.score)
from (select b.*, (select min(b2.bin) from Bins b2 where b2.bin > b.bin) as nextbin
      from Bins b
     ) b left outer join
     Scores s
     on s.score >= b.bin and (s.score < b.nextbin or b.nextbin is null)
group by b.bin, b.nextbin;

This has a similar issue with the boundaries. The easiest way to fix that is by choosing the bins well. (You can fix it in SQL but it makes the query much more complicated.)

Note in SQL Server 2012 + you can replace the correlated subquery with the lead() function.

Here is a reference for the frequency() function that will explain the notation.

Upvotes: 1

Related Questions