thewillistree Posted July 9, 2008 Report Share Posted July 9, 2008 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 - 41842 - 71843 - 01844 - 8and 1841Henrietta ARNOLDWilliam ARNOLDSusannah BIGGSFrederick BROOKSetc Quote Link to comment Share on other sites More sharing options...
xray Posted July 17, 2008 Report Share Posted July 17, 2008 Try these:Births counted by year:SELECT SUBSTRING( birthdatetr, 1, 4 ) AS year, count( * ) AS BirthsFROM tng_peopleWHERE 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 NameFROM tng_people where SUBSTRING(birthdatetr, 1, 4) <> "0000"ORDER BY year asc Quote Link to comment Share on other sites More sharing options...
thewillistree Posted July 17, 2008 Author Report Share Posted July 17, 2008 Try these:Births counted by year:SELECT SUBSTRING( birthdatetr, 1, 4 ) AS year, count( * ) AS BirthsFROM tng_peopleWHERE 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 NameFROM tng_people where SUBSTRING(birthdatetr, 1, 4) <> "0000"ORDER BY year ascThanks for that - how can I tweak it to only display years from 1500 onwards? Quote Link to comment Share on other sites More sharing options...
xray Posted July 17, 2008 Report Share Posted July 17, 2008 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 BirthsFROM tng_peopleWHERE SUBSTRING( birthdatetr, 1, 4 ) > 1500GROUP BY Year ASC AND SELECT personID, SUBSTRING(birthdatetr, 1, 4) AS year,CONCAT(firstname, " ", lastname) AS NameFROM tng_people WHERE SUBSTRING(birthdatetr, 1, 4) > 1500ORDER BY year ASC Quote Link to comment Share on other sites More sharing options...
thewillistree Posted July 17, 2008 Author Report Share Posted July 17, 2008 Try this:SELECT SUBSTRING( birthdatetr, 1, 4 ) AS Year, count( * ) AS BirthsFROM tng_peopleWHERE SUBSTRING( birthdatetr, 1, 4 ) > 1500GROUP BY Year ASC AND SELECT personID, SUBSTRING(birthdatetr, 1, 4) AS year,CONCAT(firstname, " ", lastname) AS NameFROM tng_people WHERE SUBSTRING(birthdatetr, 1, 4) > 1500ORDER BY year ASCThat's great thanks! btw - will it show Living people? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.