Ufdjrw

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

1

1

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

share|improve this question

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

1

1

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

share|improve this question

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

share|improve this question

edited Mar 7 at 8:48

Zaynul Abadin Tuhin

17.8k21134

asked Mar 7 at 8:45

MladenGMladenG

176

ID Name
_________________________
0906013123588 | Name1
1508007128536 | Name2
1603008120746 | Name3
2705983123601 | Name4
0101018125432 | Name5

share|improve this question

edited Mar 7 at 8:48

Zaynul Abadin Tuhin

17.8k21134

asked Mar 7 at 8:45

MladenGMladenG

176

share|improve this question

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

asked Mar 7 at 8:45

MladenGMladenG

176

asked Mar 7 at 8:45

MladenGMladenG

176

2 Answers
2

active

oldest

votes

5

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

share|improve this answer

edited Mar 7 at 9:17

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 | 

2

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.

share|improve this answer

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
);



);

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

Name

Email

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

Name

Email

Required, but never shown

5

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

share|improve this answer

edited Mar 7 at 9:17

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 | 

5

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

share|improve this answer

edited Mar 7 at 9:17

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

share|improve this answer

edited Mar 7 at 9:17

answered Mar 7 at 9:11

DCODCO

754417

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

share|improve this answer

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

2

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.

share|improve this answer

answered Mar 7 at 12:06

Gordon LinoffGordon Linoff

790k35314418

add a comment | 

2

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.

share|improve this answer

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.

share|improve this answer

answered Mar 7 at 12:06

Gordon LinoffGordon Linoff

790k35314418

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;

share|improve this answer

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