Jump to content
TNG Community

Births per year Report?


thewillistree

Recommended Posts

thewillistree

I'm slowly getting my head round this report thing - but can someone advise me how to create a report showing how many births occurred in each year? And one listing name?

Something like:

1841 - 4

1842 - 7

1843 - 0

1844 - 8

and

1841

Henrietta ARNOLD

William ARNOLD

Susannah BIGGS

Frederick BROOKS

etc

Link to comment
Share on other sites

Try these:

Births counted by year:

SELECT SUBSTRING( birthdatetr, 1, 4 ) AS year, count( * ) AS Births
FROM tng_people
WHERE SUBSTRING( birthdatetr, 1, 4 ) <> "0000"
GROUP BY year ASC
and Births sorted by year:
SELECT personID, SUBSTRING(birthdatetr, 1, 4) as year, CONCAT(firstname, " ", lastname) as Name
FROM tng_people where SUBSTRING(birthdatetr, 1, 4) <> "0000"
ORDER BY year asc

Link to comment
Share on other sites

thewillistree

Try these:

Births counted by year:

SELECT SUBSTRING( birthdatetr, 1, 4 ) AS year, count( * ) AS Births
FROM tng_people
WHERE SUBSTRING( birthdatetr, 1, 4 ) <> "0000"
GROUP BY year ASC
and Births sorted by year:
SELECT personID, SUBSTRING(birthdatetr, 1, 4) as year, CONCAT(firstname, " ", lastname) as Name
FROM tng_people where SUBSTRING(birthdatetr, 1, 4) <> "0000"
ORDER BY year asc

Thanks for that - how can I tweak it to only display years from 1500 onwards?

Link to comment
Share on other sites

Thanks for that - how can I tweak it to only display years from 1500 onwards?

Try this:

SELECT SUBSTRING( birthdatetr, 1, 4 ) AS Year, count( * ) AS Births
FROM tng_people
WHERE SUBSTRING( birthdatetr, 1, 4 ) > 1500
GROUP BY Year ASC
AND
SELECT personID, SUBSTRING(birthdatetr, 1, 4) AS year,
CONCAT(firstname, " ", lastname) AS Name
FROM tng_people
WHERE SUBSTRING(birthdatetr, 1, 4) > 1500
ORDER BY year ASC

Link to comment
Share on other sites

thewillistree

Try this:

SELECT SUBSTRING( birthdatetr, 1, 4 ) AS Year, count( * ) AS Births
FROM tng_people
WHERE SUBSTRING( birthdatetr, 1, 4 ) > 1500
GROUP BY Year ASC
AND
SELECT personID, SUBSTRING(birthdatetr, 1, 4) AS year,
CONCAT(firstname, " ", lastname) AS Name
FROM tng_people
WHERE SUBSTRING(birthdatetr, 1, 4) > 1500
ORDER BY year ASC

That's great thanks! btw - will it show Living people?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...