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