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
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
asked Nov 26 '18 at 22:23
user8142520user8142520
818
add a comment |
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
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
asked Nov 26 '18 at 22:23
user8142520user8142520
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
add a comment |
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
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
asked Nov 26 '18 at 22:23
user8142520user8142520
818
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
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
asked Nov 26 '18 at 22:23
user8142520user8142520
818
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
asked Nov 26 '18 at 22:23
user8142520user8142520
818
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
edited Nov 27 '18 at 0:32
Strawberry
26.3k83149
26.3k83149
asked Nov 26 '18 at 22:23
user8142520user8142520
818
asked Nov 26 '18 at 22:23
user8142520user8142520
818
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
add a comment |
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
add a comment |
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
answered Nov 26 '18 at 22:34
NickNick
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
add a comment |
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;
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
add a comment |
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
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
answered Nov 26 '18 at 22:34
NickNick
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
add a comment |
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
answered Nov 26 '18 at 22:34
NickNick
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
add a comment |
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
answered Nov 26 '18 at 22:34
NickNick
31.5k121942
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
answered Nov 26 '18 at 22:34
NickNick
31.5k121942
answered Nov 26 '18 at 22:34
NickNick
31.5k121942
answered Nov 26 '18 at 22:34
NickNick
31.5k121942
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
add a comment |
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
add a comment |
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;
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
add a comment |
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;
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
add a comment |
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;
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
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;
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
answered Nov 26 '18 at 22:28
Gordon LinoffGordon Linoff
776k35306409
776k35306409
add a comment |
add a comment |
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
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
add a comment |
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
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
add a comment |
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
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
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
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
answered Nov 26 '18 at 23:04
Himanshu AhujaHimanshu Ahuja
7022217
7022217
add a comment |
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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