SQL Server: how to create range of values based on a column with monetary values

Question

Hello, community!

I need an SQL query with the values of a SQL Server 2012 table field shown as frequency distribution.

Let’s say I have, today, the field Sales (in R$), type float.

The lowest sales recorded was R$ 0.01 and the highest was R$ 1,000,345.23.

The detail is that I’ll never know which one is bigger and which one is smaller.

It’s hundreds of sales. I want to return class frequencies. Each class will have a range of R$100,000.00.

Then my results would have to come in the form of a frequency distribution.

Thus,

The SQL Server 2012 table loads the sales values:

The query brings the values distributed in range:

Answer 1

Here is a solution that groups by quantities of 1.00 to 9.99, 10.00 to 99.99, 100.00 to 999.99 and so on:

SELECT   POWER( 10, FLOOR( LOG10( valor ) + 1 ) ) - .01 AS Teto,
         COUNT(*) AS Quantidade
FROM     valores
GROUP BY FLOOR( LOG10( valor ) + 1 )
ORDER BY Teto;

See working on SQL Fiddle.

The important thing here is FLOOR( LOG10( valor ) ) to group the number of houses, the variations of this are details.

If you change the information to GROUP BY FLOOR( valor / 1000 ), for example, the formula is scaled linearly (just adjust the number of zeros according to the desired scale)

Here’s a SQL Fiddle with the linear version.

Note: The lines of AS Base AS Teto are mere formatting for display and data conferencing. Most likely a message De 1 a 9.99 is better, and easier to be shown in the application, simply get a column with the same formula as the GROUP BY.

Answer 2

UPDATE

I preferred the version marked as reply!

ORIGINAL

This is the solution that suits me. I got it thanks to the help of @Bacco, which made me reach this link, on Daniel Hutmacher’s blog.

DECLARE @intervalo numeric(38, 18), --- amplitude de cada classe
        @min numeric(38, 18), --- menor valor da tabela
        @max numeric(38, 18), --- maior valor da tabela
        @niveis smallint=7; --- numero de classes

--- pega os valores maximos e minimos usando MIN(), MAX() e conta as linhas da tabela COUNT()
SELECT @max=MAX(vendas),
       @min=MIN(vendas)
FROM [meuBancoDeDados].[dbo].[minhaTabelaDeValores];

--- Calcula cada intervalo de classe
SET @intervalo=(@max-@min)/@niveis;

SELECT @min+@intervalo*FLOOR(vendas/@intervalo) AS fromValue,
       @min+@intervalo*FLOOR(vandas/@intervalo)+@intervalo AS toValue,
       COUNT(*) AS [count]
FROM (
      SELECT ISNULL(NULLIF(vendas, @max), @max-0.5*@intervalo)-@min AS vendas
      FROM [meuBancoDeDados].[dbo].[minhaTabelaDeValores]
      ) AS sub
GROUP BY FLOOR(vendas/@intervalo)
ORDER BY FLOOR(vendas/@intervalo);