Difference between revisions of "Range"
From SQLZOO
(Created page with "<p>Here you are shown how to break your query down by range.</p> <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE population;</so...") |
|||
| Line 21: | Line 21: | ||
</div> | </div> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
| − | SELECT (low-5) || '-' || (low+4) AS | + | SELECT (low-5) || '-' || (low+4) AS the_range, |
avgSpend | avgSpend | ||
FROM (SELECT ROUND(age,-1) AS low, | FROM (SELECT ROUND(age,-1) AS low, | ||
| Line 28: | Line 28: | ||
GROUP BY ROUND(age,-1)) t</source> | GROUP BY ROUND(age,-1)) t</source> | ||
<source lang='sql' class='def e-mysql'> | <source lang='sql' class='def e-mysql'> | ||
| − | SELECT CONCAT(low-5,'-' ,low+4) AS | + | SELECT CONCAT(low-5,'-' ,low+4) AS the_range, |
avgSpend | avgSpend | ||
FROM (SELECT ROUND(age,-1) AS low, | FROM (SELECT ROUND(age,-1) AS low, | ||
| Line 35: | Line 35: | ||
GROUP BY ROUND(age,-1)) t</source> | GROUP BY ROUND(age,-1)) t</source> | ||
<source lang='sql' class='def e-sqlserver'> | <source lang='sql' class='def e-sqlserver'> | ||
| − | SELECT STR(low-5) + '-' + STR(low+4) AS | + | SELECT STR(low-5) + '-' + STR(low+4) AS the_range, |
avgSpend | avgSpend | ||
FROM (SELECT ROUND(age,-1) AS low, | FROM (SELECT ROUND(age,-1) AS low, | ||
Revision as of 13:44, 8 August 2012
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
