3

Possible Duplicate:
In SQL, how can you “group by” in ranges?

i have table like that

price
id  |  prices
1   |   3910
2   |   4125
3   |   3740
4   |   2700
5   |   1600
6   |   2150
7   |   2430

my wished output is like that

1500-1999  |  2000-2499 | 2500-2999  | 3000-3499 | 3500-3999 | 4000-4499  |   allvalues
--------------------------------------------------------------------------------------
   1       |      2     |      1     |     0     |     2     |     1      |   7

expli :

  • 1500-1999 --> means i want count numbers which are between 1500 and 1999 and so on , with others.

  • allvalues is to count all values .

    *here is the sql fiddle if someone need to test it there.

hope all is clear , thanks for any help.

Community
  • 1
  • 1
echo_Me
  • 37,078
  • 5
  • 58
  • 78

1 Answers1

5

This is a kind of pivot using ranges. The pattern is the same for a pivot query using SUM(CASE...)

SELECT
  SUM(CASE WHEN prices BETWEEN 1500 AND 1999 THEN 1 ELSE 0 END) AS `1500-1999`,
  SUM(CASE WHEN prices BETWEEN 2000 AND 2499 THEN 1 ELSE 0 END) AS `2000-2499`,
  SUM(CASE WHEN prices BETWEEN 2500 AND 2999 THEN 1 ELSE 0 END) AS `2500-2999`,
  SUM(CASE WHEN prices BETWEEN 3000 AND 3499 THEN 1 ELSE 0 END) AS `3000-3499`,
  SUM(CASE WHEN prices BETWEEN 3500 AND 3999 THEN 1 ELSE 0 END) AS `3500-3999`,
  ...
  COUNT(*) AS `All Values`
FROM
  prices

Substitute the rest of the ranges... The CASE produces a 0 or 1 if the condition is matched and those are added up by the aggregate SUM()s.

http://sqlfiddle.com/#!2/5803e/10

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390