Count number of people using their ID number in certain age rangeHelp with this query in AccessSELECT the requested cell by using MAX() and SUM() functionstrying to pull from multiple tables and displaying the different informationBuild table in WITH clause from csvGroup rows in table by dateReturning rows between two row numbers (Between Range)How to get all child of each records of a self-referenced tableCompare arrays from mysql-colums then insert something in each row with same colum-arrayGet the values from $i , with disturbed orderoracle sql from rows to columns
Will adding a BY-SA image to a blog post make the entire post BY-SA?
Query about absorption line spectra
How do ground effect vehicles perform turns?
Proving a function is onto where f(x)=|x|.
How to color a curve
Diode in opposite direction?
Transformation of random variables and joint distributions
How must one send away the mother bird?
Why does Async/Await work properly when the loop is inside the async function and not the other way around?
Has Darkwing Duck ever met Scrooge McDuck?
Is it possible to use .desktop files to open local pdf files on specific pages with a browser?
Is there a conventional notation or name for the slip angle?
Does having a TSA Pre-Check member in your flight reservation increase the chances that everyone gets Pre-Check?
Drawing a topological "handle" with Tikz
Is a model fitted to data or is data fitted to a model?
Can somebody explain Brexit in a few child-proof sentences?
What linear sensor for a keyboard?
My friend sent me a screenshot of a transaction hash, but when I search for it I find divergent data. What happened?
Longest common substring in linear time
MAXDOP Settings for SQL Server 2014
Translation of Scottish 16th century church stained glass
Could solar power be utilized and substitute coal in the 19th Century
Two-sided logarithm inequality
Is possible to search in vim history?
Count number of people using their ID number in certain age range
Help with this query in AccessSELECT the requested cell by using MAX() and SUM() functionstrying to pull from multiple tables and displaying the different informationBuild table in WITH clause from csvGroup rows in table by dateReturning rows between two row numbers (Between Range)How to get all child of each records of a self-referenced tableCompare arrays from mysql-colums then insert something in each row with same colum-arrayGet the values from $i , with disturbed orderoracle sql from rows to columns
I have a tabel with two columns. First column is a pesonal ID number, second column is a name. I need to sum how many kids are in range between ages 5 and ages 16 (older than 5 and younger than 16) using their ID number (which contains date of birth).
Example:
ID Name
_________________________
0906013123588 | Name1
1508007128536 | Name2
1603008120746 | Name3
2705983123601 | Name4
0101018125432 | Name5
Result should be: 3
First 7 digits of an ID number represent date of birth next 6 digits are a control number (which doesnt interest me at this point).
mysql sql
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
asked Mar 7 at 8:45
MladenGMladenG
176
|
show 2 more comments
I have a tabel with two columns. First column is a pesonal ID number, second column is a name. I need to sum how many kids are in range between ages 5 and ages 16 (older than 5 and younger than 16) using their ID number (which contains date of birth).
Example:
ID Name
_________________________
0906013123588 | Name1
1508007128536 | Name2
1603008120746 | Name3
2705983123601 | Name4
0101018125432 | Name5
Result should be: 3
First 7 digits of an ID number represent date of birth next 6 digits are a control number (which doesnt interest me at this point).
mysql sql
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
asked Mar 7 at 8:45
MladenGMladenG
176
5
First of all you should extend your table with a column for persons birthday.
– DCO
Mar 7 at 8:49
1
What is the format of the date in the ID?
– cdaiga
Mar 7 at 8:49
@cdaiga day, month, year
– MladenG
Mar 7 at 8:53
@cdaiga DD/MM/YYY
– MladenG
Mar 7 at 8:55
@DCO 7 digits so it's 09/06/013 => 09/06/2013
– Daniel E.
Mar 7 at 8:57
|
show 2 more comments
I have a tabel with two columns. First column is a pesonal ID number, second column is a name. I need to sum how many kids are in range between ages 5 and ages 16 (older than 5 and younger than 16) using their ID number (which contains date of birth).
Example:
ID Name
_________________________
0906013123588 | Name1
1508007128536 | Name2
1603008120746 | Name3
2705983123601 | Name4
0101018125432 | Name5
Result should be: 3
First 7 digits of an ID number represent date of birth next 6 digits are a control number (which doesnt interest me at this point).
mysql sql
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
asked Mar 7 at 8:45
MladenGMladenG
176
I have a tabel with two columns. First column is a pesonal ID number, second column is a name. I need to sum how many kids are in range between ages 5 and ages 16 (older than 5 and younger than 16) using their ID number (which contains date of birth).
Example:
ID Name
_________________________
0906013123588 | Name1
1508007128536 | Name2
1603008120746 | Name3
2705983123601 | Name4
0101018125432 | Name5
Result should be: 3
First 7 digits of an ID number represent date of birth next 6 digits are a control number (which doesnt interest me at this point).
mysql sql
mysql sql
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
asked Mar 7 at 8:45
MladenGMladenG
176
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
asked Mar 7 at 8:45
MladenGMladenG
176
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
edited Mar 7 at 8:48
Zaynul Abadin Tuhin
17.8k21134
17.8k21134
asked Mar 7 at 8:45
MladenGMladenG
176
asked Mar 7 at 8:45
MladenGMladenG
176
asked Mar 7 at 8:45
MladenGMladenG
176
176
5
First of all you should extend your table with a column for persons birthday.
– DCO
Mar 7 at 8:49
1
What is the format of the date in the ID?
– cdaiga
Mar 7 at 8:49
@cdaiga day, month, year
– MladenG
Mar 7 at 8:53
@cdaiga DD/MM/YYY
– MladenG
Mar 7 at 8:55
@DCO 7 digits so it's 09/06/013 => 09/06/2013
– Daniel E.
Mar 7 at 8:57
|
show 2 more comments
5
First of all you should extend your table with a column for persons birthday.
– DCO
Mar 7 at 8:49
1
What is the format of the date in the ID?
– cdaiga
Mar 7 at 8:49
@cdaiga day, month, year
– MladenG
Mar 7 at 8:53
@cdaiga DD/MM/YYY
– MladenG
Mar 7 at 8:55
@DCO 7 digits so it's 09/06/013 => 09/06/2013
– Daniel E.
Mar 7 at 8:57
5
5
First of all you should extend your table with a column for persons birthday.
– DCO
Mar 7 at 8:49
First of all you should extend your table with a column for persons birthday.
– DCO
Mar 7 at 8:49
1
1
What is the format of the date in the ID?
– cdaiga
Mar 7 at 8:49
What is the format of the date in the ID?
– cdaiga
Mar 7 at 8:49
@cdaiga day, month, year
– MladenG
Mar 7 at 8:53
@cdaiga day, month, year
– MladenG
Mar 7 at 8:53
@cdaiga DD/MM/YYY
– MladenG
Mar 7 at 8:55
@cdaiga DD/MM/YYY
– MladenG
Mar 7 at 8:55
@DCO 7 digits so it's 09/06/013 => 09/06/2013
– Daniel E.
Mar 7 at 8:57
@DCO 7 digits so it's 09/06/013 => 09/06/2013
– Daniel E.
Mar 7 at 8:57
|
show 2 more comments
2 Answers
2
active
oldest
votes
The solution is to clean up your database design.
You could create a new table and transform your data to that new table programmatically.
Your new table should contain a seperated date column for date_of_birth.
I think MySql is not able to handle format yyy.
It can just handle yy or yyyy.
https://www.w3schools.com/sql/func_mysql_date_format.asp
if the format would be correct you could do something like:
SELECT *
FROM person_table
WHERE
DATEDIFF(
CURRENT_DATE,
STR_TO_DATE(
SUBSTRING(
CONVERT (ID,char)
,0,6
),'%d%m%y'
)/365
BETWEEN 5 AND 16
answered Mar 7 at 9:11
DCODCO
754417
1
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
add a comment |
I would not use datediff() for this purpose. I would simply compare the birthdate to the appropriate birthdate range:
SELECT pt.*
FROM person_table pt
WHERE STR_TO_DATE(LEFT(ID, 6), '%d%m%y') > curdate() - interval 16 year AND
STR_TO_DATE(LEFT(ID, 6), '%d%m%y') <= curdate() - interval 5 year;
You need to be careful with the two-digit years. After all, the id doesn't handle people over 100 years old very well.
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
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
);
);
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%2f55039462%2fcount-number-of-people-using-their-id-number-in-certain-age-range%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The solution is to clean up your database design.
You could create a new table and transform your data to that new table programmatically.
Your new table should contain a seperated date column for date_of_birth.
I think MySql is not able to handle format yyy.
It can just handle yy or yyyy.
https://www.w3schools.com/sql/func_mysql_date_format.asp
if the format would be correct you could do something like:
SELECT *
FROM person_table
WHERE
DATEDIFF(
CURRENT_DATE,
STR_TO_DATE(
SUBSTRING(
CONVERT (ID,char)
,0,6
),'%d%m%y'
)/365
BETWEEN 5 AND 16
answered Mar 7 at 9:11
DCODCO
754417
1
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
add a comment |
The solution is to clean up your database design.
You could create a new table and transform your data to that new table programmatically.
Your new table should contain a seperated date column for date_of_birth.
I think MySql is not able to handle format yyy.
It can just handle yy or yyyy.
https://www.w3schools.com/sql/func_mysql_date_format.asp
if the format would be correct you could do something like:
SELECT *
FROM person_table
WHERE
DATEDIFF(
CURRENT_DATE,
STR_TO_DATE(
SUBSTRING(
CONVERT (ID,char)
,0,6
),'%d%m%y'
)/365
BETWEEN 5 AND 16
answered Mar 7 at 9:11
DCODCO
754417
1
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
add a comment |
The solution is to clean up your database design.
You could create a new table and transform your data to that new table programmatically.
Your new table should contain a seperated date column for date_of_birth.
I think MySql is not able to handle format yyy.
It can just handle yy or yyyy.
https://www.w3schools.com/sql/func_mysql_date_format.asp
if the format would be correct you could do something like:
SELECT *
FROM person_table
WHERE
DATEDIFF(
CURRENT_DATE,
STR_TO_DATE(
SUBSTRING(
CONVERT (ID,char)
,0,6
),'%d%m%y'
)/365
BETWEEN 5 AND 16
answered Mar 7 at 9:11
DCODCO
754417
The solution is to clean up your database design.
You could create a new table and transform your data to that new table programmatically.
Your new table should contain a seperated date column for date_of_birth.
I think MySql is not able to handle format yyy.
It can just handle yy or yyyy.
https://www.w3schools.com/sql/func_mysql_date_format.asp
if the format would be correct you could do something like:
SELECT *
FROM person_table
WHERE
DATEDIFF(
CURRENT_DATE,
STR_TO_DATE(
SUBSTRING(
CONVERT (ID,char)
,0,6
),'%d%m%y'
)/365
BETWEEN 5 AND 16
answered Mar 7 at 9:11
DCODCO
754417
edited Mar 7 at 9:17
answered Mar 7 at 9:11
DCODCO
754417
answered Mar 7 at 9:11
DCODCO
754417
answered Mar 7 at 9:11
DCODCO
754417
754417
1
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
add a comment |
1
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
1
1
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
Thanks, I'll try that and let you know how it went.
– MladenG
Mar 7 at 9:33
add a comment |
I would not use datediff() for this purpose. I would simply compare the birthdate to the appropriate birthdate range:
SELECT pt.*
FROM person_table pt
WHERE STR_TO_DATE(LEFT(ID, 6), '%d%m%y') > curdate() - interval 16 year AND
STR_TO_DATE(LEFT(ID, 6), '%d%m%y') <= curdate() - interval 5 year;
You need to be careful with the two-digit years. After all, the id doesn't handle people over 100 years old very well.
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
add a comment |
I would not use datediff() for this purpose. I would simply compare the birthdate to the appropriate birthdate range:
SELECT pt.*
FROM person_table pt
WHERE STR_TO_DATE(LEFT(ID, 6), '%d%m%y') > curdate() - interval 16 year AND
STR_TO_DATE(LEFT(ID, 6), '%d%m%y') <= curdate() - interval 5 year;
You need to be careful with the two-digit years. After all, the id doesn't handle people over 100 years old very well.
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
add a comment |
I would not use datediff() for this purpose. I would simply compare the birthdate to the appropriate birthdate range:
SELECT pt.*
FROM person_table pt
WHERE STR_TO_DATE(LEFT(ID, 6), '%d%m%y') > curdate() - interval 16 year AND
STR_TO_DATE(LEFT(ID, 6), '%d%m%y') <= curdate() - interval 5 year;
You need to be careful with the two-digit years. After all, the id doesn't handle people over 100 years old very well.
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
I would not use datediff() for this purpose. I would simply compare the birthdate to the appropriate birthdate range:
SELECT pt.*
FROM person_table pt
WHERE STR_TO_DATE(LEFT(ID, 6), '%d%m%y') > curdate() - interval 16 year AND
STR_TO_DATE(LEFT(ID, 6), '%d%m%y') <= curdate() - interval 5 year;
You need to be careful with the two-digit years. After all, the id doesn't handle people over 100 years old very well.
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
answered Mar 7 at 12:06
Gordon LinoffGordon Linoff
790k35314418
790k35314418
add a comment |
add a comment |
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.
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%2f55039462%2fcount-number-of-people-using-their-id-number-in-certain-age-range%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
5
First of all you should extend your table with a column for persons birthday.
– DCO
Mar 7 at 8:49
1
What is the format of the date in the ID?
– cdaiga
Mar 7 at 8:49
@cdaiga day, month, year
– MladenG
Mar 7 at 8:53
@cdaiga DD/MM/YYY
– MladenG
Mar 7 at 8:55
@DCO 7 digits so it's 09/06/013 => 09/06/2013
– Daniel E.
Mar 7 at 8:57