5
From Table
A   B
_______________
1   A
3   B
6   C
7   C
8   X   
9   Y
15  Z
16  R
17  t   
23  T
43  e

How to bring this result:

Range A  Count B
_________________
1-10    6
11-20   3
21-30   1
31-40   1

This could be done using loop:

Select Count (* ) from TableA where b between @a and @b

But I would like to do it without using any loop is it possible?

ekad
  • 14,436
  • 26
  • 44
  • 46
Thunder
  • 10,366
  • 25
  • 84
  • 114

1 Answers1

2

Try this:

SELECT 
Cast(((A / 10) * 10 + 1) as varchar(100)) + '-' + Cast(((A / 10) * 10 + 10) as varchar(100)) AS 'Range A',
Count(*) as 'Count B'
 FROM TableA
GROUP BY A / 10
Tim Rogers
  • 21,297
  • 6
  • 52
  • 68
  • -1, You are using mod, you should be using integer divide `round((a / 10)+/- 0.5)` instead. – Johan Sep 29 '11 at 13:56
  • @Johan Yep, well spotted. Fixed. – Tim Rogers Sep 29 '11 at 15:13
  • The result was not in range 1-10,11-20 as expected but as 1-10,2-11 etc each of lower and upper limit was incremented by 1, so yet to figure out how to do it. – Thunder Oct 03 '11 at 04:57
  • @Thunder Tested with SQL Server (not SQLite but can't see why it would be different). Works for me - gives 1-10, 6 | 11-20, 3 | 21-30, 1 | 41-50, 1 – Tim Rogers Oct 03 '11 at 08:23
  • @Tim Rogers: One reason why ("it would be different") is SQLite might not use integer division in case both operands are integers, like SQL Server does. – Andriy M Oct 03 '11 at 13:53
  • `9` and `10` would be in different groups with this approach: `9 / 10 -> 0`, `10 / 10 -> 1`. Test your query again on the original sample but with `9` changed to `10`, and you'll see. – Andriy M Oct 03 '11 at 14:35