Recommand · October 22, 2021 0

How to calculate frequencies over deciles

Everything is recorded in a cash register database table, [dbo].[festival]. Each row in [dbo].[festival] is an order row from a festival participant. We have the following columns:

[ticket_no] int – Each participant has a unique entry ticket number, although this is not a PK since many participants order many rounds before they stagger home or are carried out.

[price] float – The price tags of the various kinds and sizes of beer served.

[quantity] int – The number of beers ordered at that price.

Now I want understand the participants better. I want the total sales of beer divided into 10 equal chunks, with the number of participants making up each decile and the absolute spending range of the decile, in rising order. Something like:

[Decile] / [Lower end of range] / [Upper end of range] / [No of participants] / [Total sales in this decile]

So, given total sales in the event of 100.000, the first two (of ten) rows could be something like:

1 / 2.50 / 4.75 / 3104 / 10.000 

2 / 4.75 / 6.50 / 1905 / 10.000

Meaning 3104 participants with the lowest spending range from 2.50 up to 4.75 stood for the first 10k of the 100k beer sales, then there were 1905 participants who spent between… etc.

How do you solve this in SQL? I failed to adapt John Cappelletti’s solution to the previous problem.