Difference between revisions of "Range"

From SQLZOO
Jump to: navigation, search
(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...")
 
 
(One intermediate revision by one user not shown)
Line 21: Line 21:
 
</div>
 
</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT (low-5) || '-' || (low+4) AS range,
+
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 range,
+
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 range,
+
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,
Line 45: Line 45:
 
<pre>SELECT age, 5*FLOOR(age/5) AS valueBucket,
 
<pre>SELECT age, 5*FLOOR(age/5) AS valueBucket,
 
       CONCAT(5*FLOOR(age/5),'-',5*FLOOR(age/5)+4) AS the_range
 
       CONCAT(5*FLOOR(age/5),'-',5*FLOOR(age/5)+4) AS the_range
   FROM population </div>
+
   FROM population </pre>
 +
</div>
 
</div>
 
</div>
 
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Latest revision as of 09:06, 11 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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense