How to run sql query recursively?












0















For example, there is a accounts table has: 



account_id | ......    

000        | ......    

001        | ......    

004        | ......    

010        | ......   

.....   

198        | ......


I want to get the distribution of account id, instead of running following query again and again, is there any smarter way to get id count for 000-010, 010-020, ..., 190-200? Thanks



SELECT count(account_id)

FROM accounts

WHERE account_id >= '000' AND  account_id <= '010';





mysql sql







share|improve this question

























  • You mean iteratively. Although I'm suspicious of trying to segment ids this way, because (if they're auto-gen), the values are meaningless, and you should be using some natural key.

    – Clockwork-Muse
    Nov 26 '18 at 22:47
















0















For example, there is a accounts table has: 



account_id | ......    

000        | ......    

001        | ......    

004        | ......    

010        | ......   

.....   

198        | ......


I want to get the distribution of account id, instead of running following query again and again, is there any smarter way to get id count for 000-010, 010-020, ..., 190-200? Thanks



SELECT count(account_id)

FROM accounts

WHERE account_id >= '000' AND  account_id <= '010';





mysql sql







share|improve this question

























  • You mean iteratively. Although I'm suspicious of trying to segment ids this way, because (if they're auto-gen), the values are meaningless, and you should be using some natural key.

    – Clockwork-Muse
    Nov 26 '18 at 22:47














0












0








0








For example, there is a accounts table has: 



account_id | ......    

000        | ......    

001        | ......    

004        | ......    

010        | ......   

.....   

198        | ......


I want to get the distribution of account id, instead of running following query again and again, is there any smarter way to get id count for 000-010, 010-020, ..., 190-200? Thanks



SELECT count(account_id)

FROM accounts

WHERE account_id >= '000' AND  account_id <= '010';





mysql sql







share|improve this question
















For example, there is a accounts table has: 



account_id | ......    

000        | ......    

001        | ......    

004        | ......    

010        | ......   

.....   

198        | ......


I want to get the distribution of account id, instead of running following query again and again, is there any smarter way to get id count for 000-010, 010-020, ..., 190-200? Thanks



SELECT count(account_id)

FROM accounts

WHERE account_id >= '000' AND  account_id <= '010';





mysql sql




mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 27 '18 at 0:32









Strawberry

26.3k83149




26.3k83149










asked Nov 26 '18 at 22:23









user8142520user8142520

818




818













  • You mean iteratively. Although I'm suspicious of trying to segment ids this way, because (if they're auto-gen), the values are meaningless, and you should be using some natural key.

    – Clockwork-Muse
    Nov 26 '18 at 22:47



















  • You mean iteratively. Although I'm suspicious of trying to segment ids this way, because (if they're auto-gen), the values are meaningless, and you should be using some natural key.

    – Clockwork-Muse
    Nov 26 '18 at 22:47

















You mean iteratively. Although I'm suspicious of trying to segment ids this way, because (if they're auto-gen), the values are meaningless, and you should be using some natural key.

– Clockwork-Muse
Nov 26 '18 at 22:47





You mean iteratively. Although I'm suspicious of trying to segment ids this way, because (if they're auto-gen), the values are meaningless, and you should be using some natural key.

– Clockwork-Muse
Nov 26 '18 at 22:47












3 Answers
3






active

oldest

votes


















1














You can divide the account_id by 10 to create a range and then group by the divided result to get the result you want:



SELECT CONCAT(LPAD(FLOOR(account_id/10)*10,3, '0'), '-', LPAD(FLOOR(account_id/10)*10+9, 3, '0')) AS `range`,

       COUNT(*) AS number

FROM accounts

GROUP BY `range`


Output (for some sample data in my demo):



range       number

000-009     3

010-019     2

020-029     1

030-039     1

040-049     1

050-059     2


Demo on dbfiddle






share|improve this answer
























  • Amazing! Thank you!

    – user8142520
    Nov 26 '18 at 23:03






  • 1





    Note that this will omit empty buckets, so just be aware of that when processing the result

    – Strawberry
    Nov 27 '18 at 0:33



















1














You would use group by:



select (case when account_id >= '000' and account_id <= '010' then '000-010'

             when account_id >= '011' and account_id <= '020' then '011-020'

             when account_id >= '021' and account_id <= '030' then '021-030'

             . . .

        end) as account_id_grp,

       count(*)

from accounts

group by account_id_grp

order by account_id_grp;





share|improve this answer































    0














         select t1.account_id ||'-'||t2.
    
     account_id,count(*) from 
    
     table t1 where account_id IN (Select account_id from 
    
      table t2 where t2.account_id-t1.account_id=10)`



    I tried like taking difference of the account ids in the tables via corelated subquery







    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490031%2fhow-to-run-sql-query-recursively%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      1














      You can divide the account_id by 10 to create a range and then group by the divided result to get the result you want:



      SELECT CONCAT(LPAD(FLOOR(account_id/10)*10,3, '0'), '-', LPAD(FLOOR(account_id/10)*10+9, 3, '0')) AS `range`,
      
       COUNT(*) AS number
      
FROM accounts
      
GROUP BY `range`


      Output (for some sample data in my demo):



      range       number
      
000-009     3
      
010-019     2
      
020-029     1
      
030-039     1
      
040-049     1
      
050-059     2


      Demo on dbfiddle






      share|improve this answer
























      • Amazing! Thank you!

        – user8142520
        Nov 26 '18 at 23:03






      • 1





        Note that this will omit empty buckets, so just be aware of that when processing the result

        – Strawberry
        Nov 27 '18 at 0:33
















      1














      You can divide the account_id by 10 to create a range and then group by the divided result to get the result you want:



      SELECT CONCAT(LPAD(FLOOR(account_id/10)*10,3, '0'), '-', LPAD(FLOOR(account_id/10)*10+9, 3, '0')) AS `range`,
      
       COUNT(*) AS number
      
FROM accounts
      
GROUP BY `range`


      Output (for some sample data in my demo):



      range       number
      
000-009     3
      
010-019     2
      
020-029     1
      
030-039     1
      
040-049     1
      
050-059     2


      Demo on dbfiddle






      share|improve this answer
























      • Amazing! Thank you!

        – user8142520
        Nov 26 '18 at 23:03






      • 1





        Note that this will omit empty buckets, so just be aware of that when processing the result

        – Strawberry
        Nov 27 '18 at 0:33














      1












      1








      1







      You can divide the account_id by 10 to create a range and then group by the divided result to get the result you want:



      SELECT CONCAT(LPAD(FLOOR(account_id/10)*10,3, '0'), '-', LPAD(FLOOR(account_id/10)*10+9, 3, '0')) AS `range`,
      
       COUNT(*) AS number
      
FROM accounts
      
GROUP BY `range`


      Output (for some sample data in my demo):



      range       number
      
000-009     3
      
010-019     2
      
020-029     1
      
030-039     1
      
040-049     1
      
050-059     2


      Demo on dbfiddle






      share|improve this answer













      You can divide the account_id by 10 to create a range and then group by the divided result to get the result you want:



      SELECT CONCAT(LPAD(FLOOR(account_id/10)*10,3, '0'), '-', LPAD(FLOOR(account_id/10)*10+9, 3, '0')) AS `range`,
      
       COUNT(*) AS number
      
FROM accounts
      
GROUP BY `range`


      Output (for some sample data in my demo):



      range       number
      
000-009     3
      
010-019     2
      
020-029     1
      
030-039     1
      
040-049     1
      
050-059     2


      Demo on dbfiddle







      share|improve this answer












      share|improve this answer



      share|improve this answer










      answered Nov 26 '18 at 22:34









      NickNick

      31.5k121942




      31.5k121942













      • Amazing! Thank you!

        – user8142520
        Nov 26 '18 at 23:03






      • 1





        Note that this will omit empty buckets, so just be aware of that when processing the result

        – Strawberry
        Nov 27 '18 at 0:33



















      • Amazing! Thank you!

        – user8142520
        Nov 26 '18 at 23:03






      • 1





        Note that this will omit empty buckets, so just be aware of that when processing the result

        – Strawberry
        Nov 27 '18 at 0:33

















      Amazing! Thank you!

      – user8142520
      Nov 26 '18 at 23:03





      Amazing! Thank you!

      – user8142520
      Nov 26 '18 at 23:03




      1




      1





      Note that this will omit empty buckets, so just be aware of that when processing the result

      – Strawberry
      Nov 27 '18 at 0:33





      Note that this will omit empty buckets, so just be aware of that when processing the result

      – Strawberry
      Nov 27 '18 at 0:33













      1














      You would use group by:



      select (case when account_id >= '000' and account_id <= '010' then '000-010'
      
             when account_id >= '011' and account_id <= '020' then '011-020'
      
             when account_id >= '021' and account_id <= '030' then '021-030'
      
             . . .
      
        end) as account_id_grp,
      
       count(*)
      
from accounts
      
group by account_id_grp
      
order by account_id_grp;





      share|improve this answer




























        1














        You would use group by:



        select (case when account_id >= '000' and account_id <= '010' then '000-010'
        
             when account_id >= '011' and account_id <= '020' then '011-020'
        
             when account_id >= '021' and account_id <= '030' then '021-030'
        
             . . .
        
        end) as account_id_grp,
        
       count(*)
        
from accounts
        
group by account_id_grp
        
order by account_id_grp;





        share|improve this answer


























          1












          1








          1







          You would use group by:



          select (case when account_id >= '000' and account_id <= '010' then '000-010'
          
             when account_id >= '011' and account_id <= '020' then '011-020'
          
             when account_id >= '021' and account_id <= '030' then '021-030'
          
             . . .
          
        end) as account_id_grp,
          
       count(*)
          
from accounts
          
group by account_id_grp
          
order by account_id_grp;





          share|improve this answer













          You would use group by:



          select (case when account_id >= '000' and account_id <= '010' then '000-010'
          
             when account_id >= '011' and account_id <= '020' then '011-020'
          
             when account_id >= '021' and account_id <= '030' then '021-030'
          
             . . .
          
        end) as account_id_grp,
          
       count(*)
          
from accounts
          
group by account_id_grp
          
order by account_id_grp;






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 26 '18 at 22:28









          Gordon LinoffGordon Linoff

          776k35306409




          776k35306409























              0














                   select t1.account_id ||'-'||t2.
              
     account_id,count(*) from 
              
     table t1 where account_id IN (Select account_id from 
              
      table t2 where t2.account_id-t1.account_id=10)`



              I tried like taking difference of the account ids in the tables via corelated subquery







              share|improve this answer




























                0














                     select t1.account_id ||'-'||t2.
                
     account_id,count(*) from 
                
     table t1 where account_id IN (Select account_id from 
                
      table t2 where t2.account_id-t1.account_id=10)`



                I tried like taking difference of the account ids in the tables via corelated subquery







                share|improve this answer


























                  0












                  0








                  0







                       select t1.account_id ||'-'||t2.
                  
     account_id,count(*) from 
                  
     table t1 where account_id IN (Select account_id from 
                  
      table t2 where t2.account_id-t1.account_id=10)`



                  I tried like taking difference of the account ids in the tables via corelated subquery







                  share|improve this answer













                       select t1.account_id ||'-'||t2.
                  
     account_id,count(*) from 
                  
     table t1 where account_id IN (Select account_id from 
                  
      table t2 where t2.account_id-t1.account_id=10)`



                  I tried like taking difference of the account ids in the tables via corelated subquery








                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 26 '18 at 23:04









                  Himanshu AhujaHimanshu Ahuja

                  7022217




                  7022217






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid …



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490031%2fhow-to-run-sql-query-recursively%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      -

                      Popular posts from this blog

                      A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                      Image
                      0 I found a lot of questions abount appendices and ToC. Many users want appendices to be grouped in an Appendix part, however some problems arise with ToC, hyperref, PDF viewer bookmarks, and so on. There are different solutions which require extra packages, command patching and other extra code, however none of them satisfies me. I almost found an easy way to accomplish a good result, where appendices are added to bookmarks in the right way and hyperref links point to the right page. However, the number of the "Appendix" part page is wrong (it's the number of appendix A). Is there any EASY way to fix that? This is a MWE: documentclass{book} usepackage[nottoc,notlot,notlof]{tocbibind} usepackage{hyperref} begin{document} frontmatter tableofcontents mainmatter part{First} chapter{...
                      Read more

                      Calculate evaluation metrics using cross_val_predict sklearn

                      Image
                      1 In the sklearn.model_selection.cross_val_predict page it is stated: Generate cross-validated estimates for each input data point. It is not appropriate to pass these predictions into an evaluation metric. Can someone explain what does it mean? If this gives estimate of Y (y prediction) for every Y (true Y), why can't I calculate metrics such as RMSE or coefficient of determination using these results? python scikit-learn cross-validation share | improve this question edited Nov 28 '18 at 17:52 desertnaut 20.3k 7 43 79 ...
                      Read more

                      Insert data from modal to MySQL (multiple modal on website)

                      Image
                      0 I have problem with inserting data to MySQL from modal. My modal: <a href="#" class="badge badge-pill badge-success">6 komentarzy</a> <a href="#" class="badge badge-pill badge-danger">brak komentarzy</a> <a data-toggle="modal" href="#add_desk_comm_{$desk_['desk_id']}" data-target="#add_desk_comm_{$desk_['desk_id']}" class="ediiit">(dodaj)</a> <div class="modal fade" id="add_desk_comm_{$desk_['desk_id']}" tabindex="-1" role="dialog" aria-labelledby="edit_printer" aria-hidden="true"> <div class="modal-dialog" role="document"> ...
                      Read more