sql server - sumar - Agrupar por un mismo rango de valores múltiples con suma y cuentas en SQL

sql contar registros agrupados

Question

Quiero agrupar diferentes puntos en el mismo rango por filas. Ejemplo:

Amount1  |     Amount2 
------------------------  
20,00    |     30,00
35,00    |     32,00
12,00    |     51,00
101,00   |     100,00

Aquí el resultado debería ser;

Range    |TotalAmount1 |TotalAmount2 | CountAmount1 | CountAmount2 | RateOfCountAmount1       
-----------------------------------------------------------------------------  
0-50     |  67,00      |  62,00      |   3          |   1          |  %75
50-100   |  0,00       |  151,00     |   0          |   2          |  %0
100+     |  101,00     |  0,00       |   1          |   0          |  %25
Total    |  168,00     |  213,00     |   4          |   3          |  %100

Aquí está el ejemplo: http://sqlfiddle.com/#!9/05fd3

Answer 1

puedes consultar de esta manera

;with cte as (
select case when amount1 < 50 then ''0-50'' 
            when amount1 between 50.01 and 100 then ''50-100''
            when amount1 > 100 then ''100+'' end as rngamt1,
        case when amount2 < 50 then ''0-50'' 
            when amount2 between 5.01 and 100 then ''50-100''
            when amount2 > 100 then ''100+'' end as rngamt2,
                 * from amounts
), cte2 as (select coalesce(rngamt1, rngamt2) as [Range], isnull(a.TotalAmount1,0) as TotalAmount1, isnull(b.TotalAmount2, 0) as TotalAmount2, isnull( a.TotalCount1 , 0) as TotalCount1, isnull(b.TotalCount2, 0) as Totalcount2 from
(select rngamt1, sum(amount1) TotalAmount1, count(amount1) TotalCount1 from cte c
group by rngamt1) a
full join 
(select rngamt2, sum(amount2) TotalAmount2, count(amount2) TotalCount2 from cte c
group by rngamt2) b
on a.rngamt1 = b.rngamt2
) 
select *, (TotalCount1 * 100 )/sum(TotalCount1) over () as RateCount1
 from cte2 
union
select ''Total'' as [Range], sum(TotalAmount1) as TotalAmount1, sum(totalAmount2) as TotalAmount2,    
  sum(TotalCount1) as TotalCount1, sum(Totalcount2) as TotalCount2, (sum(TotalCount1)*100)/Sum(TotalCount1) as RateCount1 from cte2