# Range

Jump to: navigation, search

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 the_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 the_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 the_range,
avgSpend
FROM (SELECT ROUND(age,-1) AS low,
AVG(spend)    AS avgSpend
FROM population
GROUP BY ROUND(age,-1)) t
```