Range - Revision history

http://sqlzoo.net/w/index.php?title=Range&feed=atom&action=history Range - Revision history 2013-06-19T17:58:41Z Revision history for this page on the wiki MediaWiki 1.20.4 http://sqlzoo.net/w/index.php?title=Range&diff=3520&oldid=prev Andr3w at 09:06, 11 August 2012 2012-08-11T09:06:38Z

<p></p> <table class='diff diff-contentalign-left'> <col class='diff-marker' /> <col class='diff-content' /> <col class='diff-marker' /> <col class='diff-content' /> <tr style='vertical-align: top;'> <td colspan='2' style="background-color: white; color:black;">← Older revision</td> <td colspan='2' style="background-color: white; color:black;">Revision as of 09:06, 11 August 2012</td> </tr><tr><td colspan="2" class="diff-lineno">Line 45:</td> <td colspan="2" class="diff-lineno">Line 45:</td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><pre>SELECT age, 5*FLOOR(age/5) AS valueBucket,</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><pre>SELECT age, 5*FLOOR(age/5) AS valueBucket,</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>       CONCAT(5*FLOOR(age/5),'-',5*FLOOR(age/5)+4) AS the_range</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>       CONCAT(5*FLOOR(age/5),'-',5*FLOOR(age/5)+4) AS the_range</div></td></tr> <tr><td class='diff-marker'>−</td><td style="background: #ffa; color:black; font-size: smaller;"><div>   FROM population </div></div></td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div>   FROM population <ins class="diffchange diffchange-inline"></pre></ins></div></td></tr> <tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div></div></div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div></div></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div></div></div></td></tr> <tr><td class='diff-marker'>−</td><td style="background: #ffa; color:black; font-size: smaller;"><div><del style="color: red; font-weight: bold; text-decoration: none;"></del></div></td><td colspan="2"> </td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>{{Hacks Ref}}</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>{{Hacks Ref}}</div></td></tr> </table>

Andr3w http://sqlzoo.net/w/index.php?title=Range&diff=3318&oldid=prev Connor at 12:44, 8 August 2012 2012-08-08T12:44:20Z

<p></p> <table class='diff diff-contentalign-left'> <col class='diff-marker' /> <col class='diff-content' /> <col class='diff-marker' /> <col class='diff-content' /> <tr style='vertical-align: top;'> <td colspan='2' style="background-color: white; color:black;">← Older revision</td> <td colspan='2' style="background-color: white; color:black;">Revision as of 12:44, 8 August 2012</td> </tr><tr><td colspan="2" class="diff-lineno">Line 21:</td> <td colspan="2" class="diff-lineno">Line 21:</td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div></div></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div></div></div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><source lang='sql' class='def'></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><source lang='sql' class='def'></div></td></tr> <tr><td class='diff-marker'>−</td><td style="background: #ffa; color:black; font-size: smaller;"><div>SELECT (low-5) || '-' || (low+4) AS <del class="diffchange diffchange-inline">range</del>,</div></td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div>SELECT (low-5) || '-' || (low+4) AS <ins class="diffchange diffchange-inline">the_range</ins>,</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>         avgSpend</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>         avgSpend</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>   FROM (SELECT ROUND(age,-1) AS low,</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>   FROM (SELECT ROUND(age,-1) AS low,</div></td></tr> <tr><td colspan="2" class="diff-lineno">Line 28:</td> <td colspan="2" class="diff-lineno">Line 28:</td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>           GROUP BY ROUND(age,-1)) t</source></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>           GROUP BY ROUND(age,-1)) t</source></div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><source lang='sql' class='def e-mysql'></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><source lang='sql' class='def e-mysql'></div></td></tr> <tr><td class='diff-marker'>−</td><td style="background: #ffa; color:black; font-size: smaller;"><div>SELECT CONCAT(low-5,'-' ,low+4) AS <del class="diffchange diffchange-inline">range</del>,</div></td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div>SELECT CONCAT(low-5,'-' ,low+4) AS <ins class="diffchange diffchange-inline">the_range</ins>,</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>         avgSpend</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>         avgSpend</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>   FROM (SELECT ROUND(age,-1) AS low,</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>   FROM (SELECT ROUND(age,-1) AS low,</div></td></tr> <tr><td colspan="2" class="diff-lineno">Line 35:</td> <td colspan="2" class="diff-lineno">Line 35:</td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>           GROUP BY ROUND(age,-1)) t</source></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>           GROUP BY ROUND(age,-1)) t</source></div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><source lang='sql' class='def e-sqlserver'></div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div><source lang='sql' class='def e-sqlserver'></div></td></tr> <tr><td class='diff-marker'>−</td><td style="background: #ffa; color:black; font-size: smaller;"><div>SELECT STR(low-5) + '-' + STR(low+4) AS <del class="diffchange diffchange-inline">range</del>,</div></td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div>SELECT STR(low-5) + '-' + STR(low+4) AS <ins class="diffchange diffchange-inline">the_range</ins>,</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>         avgSpend</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>         avgSpend</div></td></tr> <tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>   FROM (SELECT ROUND(age,-1) AS low,</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>   FROM (SELECT ROUND(age,-1) AS low,</div></td></tr> </table>

Connor http://sqlzoo.net/w/index.php?title=Range&diff=3317&oldid=prev Connor: 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..." 2012-08-08T12:42:49Z

<p>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..."</p> <p><b>New page</b></p><div><p>Here you are shown how to break your query down by range.</p><br /> <div class='ht'><br /> <div class=params>schema:scott</div><br /> <source lang=sql class='tidy'>DROP TABLE population;</source><br /> <source lang=sql class='setup'>CREATE TABLE population (<br /> id INT,<br /> age INT,<br /> spend INT );<br /> INSERT INTO population VALUES (1,34,100);<br /> INSERT INTO population VALUES (2,31,110);<br /> INSERT INTO population VALUES (3,24,140);<br /> INSERT INTO population VALUES (4,35,130);<br /> INSERT INTO population VALUES (5,39,120);<br /> </source><br /> <div><br /> <p>In this example if you want to see how much different age groups spend</p><br /> <p>then you will have to group the individuals in specific ranges.</p><br /> <p>First you use <code>ROUND</code> to group together the different age groups.</p><br /> <p>You then use the <code>AVG</code> function to find the average they spend.</p><br /> <p>Finally you use a <code>CONCAT</code> function to make the age ranges more clear.</p><br /> </div><br /> <source lang='sql' class='def'><br /> SELECT (low-5) || '-' || (low+4) AS range,<br /> avgSpend<br /> FROM (SELECT ROUND(age,-1) AS low,<br /> AVG(spend) AS avgSpend<br /> FROM population<br /> GROUP BY ROUND(age,-1)) t</source><br /> <source lang='sql' class='def e-mysql'><br /> SELECT CONCAT(low-5,'-' ,low+4) AS range,<br /> avgSpend<br /> FROM (SELECT ROUND(age,-1) AS low,<br /> AVG(spend) AS avgSpend<br /> FROM population<br /> GROUP BY ROUND(age,-1)) t</source><br /> <source lang='sql' class='def e-sqlserver'><br /> SELECT STR(low-5) + '-' + STR(low+4) AS range,<br /> avgSpend<br /> FROM (SELECT ROUND(age,-1) AS low,<br /> AVG(spend) AS avgSpend<br /> FROM population<br /> GROUP BY ROUND(age,-1)) t</source><br /> <br /> <div class="ecomm e-mysql" style="display: none">You can also use the <code>FLOOR</code> function:<br /> <pre>SELECT age, 5*FLOOR(age/5) AS valueBucket,<br /> CONCAT(5*FLOOR(age/5),'-',5*FLOOR(age/5)+4) AS the_range<br /> FROM population </div><br /> </div><br /> <br /> {{Hacks Ref}}</div>

Connor