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

4

Hello, community!

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

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

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

The detail is that I will never know which is the largest and the smallest selling.

There are hundreds of sales. I want to return the class frequencies. Each class will have a range of $ 100,000.00.

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

So,

The SQL Server 2012 table loads sales values:

Thequerybringsthevaluesdistributedinrange:

    
asked by anonymous 30.11.2015 / 20:46

2 answers

3

Here is a solution that groups by quantities from 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 staggered linearly (simply 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 merely formatting for display and data conferencing. Most likely a De 1 a 9.99 message gets better, and easier to show in the application, just get a column with the same formula as GROUP BY .

    
30.11.2015 / 21:26
1

UPDATE

I preferred the version marked as a reply!

ORIGINAL

This is the solution that caters to me. I got it thanks to @Bacco's help, which made me reach this link , at blog by Daniel Hutmacher.

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);
    
30.11.2015 / 21:30