Range

Here you are shown how to break your query down by range.

schema:scott

DROP TABLE population;

CREATE TABLE population (
   id INT,
   age INT,
   spend INT );
INSERT INTO population VALUES (1,34,100);
INSERT INTO population VALUES (2,31,110);
INSERT INTO population VALUES (3,24,140);
INSERT INTO population VALUES (4,35,130);
INSERT INTO population VALUES (5,39,120);

In this example if you want to see how much different age groups spend

then you will have to group the individuals in specific ranges.

First you use ROUND to group together the different age groups.

You then use the AVG function to find the average they spend.

Finally you use a CONCAT function to make the age ranges more clear.

SELECT (low-5) || '-' || (low+4) AS range,
        avgSpend
  FROM (SELECT ROUND(age,-1) AS low,
               AVG(spend)    AS avgSpend
          FROM population
          GROUP BY ROUND(age,-1)) t

SELECT CONCAT(low-5,'-' ,low+4) AS range,
        avgSpend
  FROM (SELECT ROUND(age,-1) AS low,
               AVG(spend)    AS avgSpend
          FROM population
          GROUP BY ROUND(age,-1)) t

SELECT STR(low-5) + '-' + STR(low+4) AS range,
        avgSpend
  FROM (SELECT ROUND(age,-1) AS low,
               AVG(spend)    AS avgSpend
          FROM population
          GROUP BY ROUND(age,-1)) t

You can also use the FLOOR function:

SELECT age, 5*FLOOR(age/5) AS valueBucket,
       CONCAT(5*FLOOR(age/5),'-',5*FLOOR(age/5)+4) AS the_range
  FROM population </div>
</div>

{{Hacks Ref}}