i have table values this:
date(in ymd) column1 column2 2013-08-08 0 6 2013-08-08 1 87 2013-08-08 2.8 23 2013-08-08 3.87 12 2013-08-08 0.111 65 2013-08-08 5 98 2013-08-08 7.876 67 2013-08-09 4.32 8 2013-08-09 4 2 2013-08-09 76.05 100 2013-08-09 32 9 i want sum of column2 in range of column1 (i.e. add column2 values falls in column1 range). result this:
[0-1] 158 [1-2] 0 [2-3] 23 [3-4] 14 [4-5] 106 [5-6] 0 [6-7] 0 [7-8] 67 [8-9] 0 [10+] 109 initially getting result below query , working fine.
select [range], [totalsum] ( select case when column1 >= 0 , column1 <= 1 '[0-1]' when column1 > 1 , column1 <= 2 '[1-2]' when column1 > 2 , column1 <= 3 '[2-3]' when column1 > 3 , column1 <= 4 '[3-4]' when column1 > 4 , column1 <= 5 '[4-5]' when column1 > 5 , column1 <= 6 '[5-6]' when column1 > 6 , column1 <= 7 '[6-7]' when column1 > 7 , column1 <= 8 '[7-8]' when column1 > 8 , column1 <= 9 '[8-9]' when column1 > 9 , column1 <= 10 '[9-10]' else '[10+]' end [range], sum(column2) [totalsum] dbo.table group case when column1 >= 0 , column1 <= 1 '[0-1]' when column1 > 1 , column1 <= 2 '[1-2]' when column1 > 2 , column1 <= 3 '[2-3]' when column1 > 3 , column1 <= 4 '[3-4]' when column1 > 4 , column1 <= 5 '[4-5]' when column1 > 5 , column1 <= 6 '[5-6]' when column1 > 6 , column1 <= 7 '[6-7]' when column1 > 7 , column1 <= 8 '[7-8]' when column1 > 8 , column1 <= 9 '[8-9]' when column1 > 9 , column1 <= 10 '[9-10]' else '[10+]' end ) t1 order case [range] when '[0-1]' 0 when '[1-2]' 1 when '[2-3]' 2 when '[3-4]' 3 when '[4-5]' 4 when '[5-6]' 5 when '[6-7]' 6 when '[7-8]' 7 when '[8-9]' 8 when '[9-10]' 9 when '[10+]' 10 end now have requirement have apply filter, here eg "where date between '2013-08-09' , '2013-08-09' " though return correct values range data available i.e. [3-4], [4-5] , , [10+]
please can suggest there way range , show 0 in totalsum column there no rows range.
what changes should make in query show range values values in totalsum column(it should either 0 or sum of column2 range).
if other idea achieve this, please suggest. appreciated.
with cte ( select 0 rng_start, 1 rng_end union select rng_end rng_start, case when rng_end = 10 null else rng_end + 1 end rng_end cte rng_end <= 10 ), cte2 ( select '[' + cast(c.rng_start nvarchar(max)) + isnull('-' + cast(c.rng_end nvarchar(max)), '+') + ']' rng_str, case when c.rng_start = 0 -1 else c.rng_start end rng_start, c.rng_end cte c ) select c.rng_str, isnull(sum(t.column2), 0) cte2 c left outer join table1 t on t.column1 > c.rng_start , (t.column1 <= c.rng_end or c.rng_end null) , t.[date] between '2013-08-09' , '2013-08-09' group c.rng_str, c.rng_start order c.rng_start 