CODEWITHSUNDEEP
sql-servergroup-byrange
OtherArch
OtherArch

Reputation: 85

Grouping by a same range of multiple values with sum and counts in SQL

I want to group different colons in same range by row. Example:

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

Here result should be;

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

Here is the example : http://sqlfiddle.com/#!9/05fd3

Upvotes: 4

Views: 92

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

you can query like this

;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

Upvotes: 5

Related Questions