Report: Number of Deaths in year-ranges
This report lists (as written below) the number of Deaths in 10-year ranges between 1500 - 1949.
It should be quite easy to adjust the ranges and/or select a place or an area.
The resulting table can f.ex. be used to find increased number of deaths during f.ex. the Salem witch trials or the "Little Ice Age".
The original SQL was written by Giorgos Betsos and was found here: StackOverflow.com
The report was uploaded by Erik Hoppe
NOTE: Henny Savenije has posted a similar report here: Report:_individuals:_death_frequency_by_decades
The differences:
- You are not limited to equal ranges but can use different ranges for different periods of time: A range can be a single day if you like.
- You can also set the earliest and latest dates to whatever you like.
- I don't have a graph.
Installation
- Go to the Reports section on the Administration page and click on the Add New tab.
- Fill in the Report name and remember to click on the Active button if you want your users to have access to it.
- Scroll to the bottom where it says "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:"
and copy and paste the Mysql code below.
SELECT x.Year, COALESCE(Total, 0) AS Total
FROM (
SELECT "Bef. 1500" AS Year
UNION SELECT "1500 - 1509"
UNION SELECT "1510 - 1519"
UNION SELECT "1520 - 1529"
UNION SELECT "1530 - 1539"
UNION SELECT "1540 - 1549"
UNION SELECT "1550 - 1559"
UNION SELECT "1560 - 1569"
UNION SELECT "1570 - 1579"
UNION SELECT "1580 - 1589"
UNION SELECT "1590 - 1599"
UNION SELECT "1600 - 1609"
UNION SELECT "1610 - 1619"
UNION SELECT "1620 - 1629"
UNION SELECT "1630 - 1639"
UNION SELECT "1640 - 1649"
UNION SELECT "1650 - 1659"
UNION SELECT "1660 - 1669"
UNION SELECT "1670 - 1679"
UNION SELECT "1680 - 1689"
UNION SELECT "1690 - 1699"
UNION SELECT "1700 - 1709"
UNION SELECT "1710 - 1719"
UNION SELECT "1720 - 1729"
UNION SELECT "1730 - 1739"
UNION SELECT "1740 - 1749"
UNION SELECT "1750 - 1759"
UNION SELECT "1760 - 1769"
UNION SELECT "1770 - 1779"
UNION SELECT "1780 - 1789"
UNION SELECT "1790 - 1799"
UNION SELECT "1800 - 1809"
UNION SELECT "1810 - 1819"
UNION SELECT "1820 - 1829"
UNION SELECT "1830 - 1839"
UNION SELECT "1840 - 1849"
UNION SELECT "1850 - 1859"
UNION SELECT "1860 - 1869"
UNION SELECT "1870 - 1879"
UNION SELECT "1880 - 1889"
UNION SELECT "1890 - 1899"
UNION SELECT "1900 - 1909"
UNION SELECT "1910 - 1919"
UNION SELECT "1920 - 1929"
UNION SELECT "1930 - 1939"
UNION SELECT "1940 - 1949"
UNION SELECT "1950 and later" ) x
LEFT JOIN (
SELECT
CASE when deathdatetr > "0000-00-00" and deathdatetr < "1500-00-00" then "Bef. 1500"
when deathdatetr >= "1500-00-00" and deathdatetr < "1510-00-00" then "1500 - 1509"
when deathdatetr >= "1510-00-00" and deathdatetr < "1520-00-00" then "1510 - 1519"
when deathdatetr >= "1520-00-00" and deathdatetr < "1530-00-00" then "1520 - 1529"
when deathdatetr >= "1530-00-00" and deathdatetr < "1540-00-00" then "1530 - 1539"
when deathdatetr >= "1540-00-00" and deathdatetr < "1550-00-00" then "1540 - 1549"
when deathdatetr >= "1550-00-00" and deathdatetr < "1560-00-00" then "1550 - 1559"
when deathdatetr >= "1560-00-00" and deathdatetr < "1570-00-00" then "1560 - 1569"
when deathdatetr >= "1570-00-00" and deathdatetr < "1580-00-00" then "1570 - 1579"
when deathdatetr >= "1580-00-00" and deathdatetr < "1590-00-00" then "1580 - 1589"
when deathdatetr >= "1590-00-00" and deathdatetr < "1600-00-00" then "1590 - 1599"
when deathdatetr >= "1600-00-00" and deathdatetr < "1610-00-00" then "1600 - 1609"
when deathdatetr >= "1610-00-00" and deathdatetr < "1620-00-00" then "1610 - 1619"
when deathdatetr >= "1620-00-00" and deathdatetr < "1630-00-00" then "1620 - 1629"
when deathdatetr >= "1630-00-00" and deathdatetr < "1640-00-00" then "1630 - 1639"
when deathdatetr >= "1640-00-00" and deathdatetr < "1650-00-00" then "1640 - 1649"
when deathdatetr >= "1650-00-00" and deathdatetr < "1660-00-00" then "1650 - 1659"
when deathdatetr >= "1660-00-00" and deathdatetr < "1670-00-00" then "1660 - 1669"
when deathdatetr >= "1670-00-00" and deathdatetr < "1680-00-00" then "1670 - 1679"
when deathdatetr >= "1680-00-00" and deathdatetr < "1690-00-00" then "1680 - 1689"
when deathdatetr >= "1690-00-00" and deathdatetr < "1700-00-00" then "1690 - 1699"
when deathdatetr >= "1700-00-00" and deathdatetr < "1710-00-00" then "1700 - 1709"
when deathdatetr >= "1710-00-00" and deathdatetr < "1720-00-00" then "1710 - 1719"
when deathdatetr >= "1720-00-00" and deathdatetr < "1730-00-00" then "1720 - 1729"
when deathdatetr >= "1730-00-00" and deathdatetr < "1740-00-00" then "1730 - 1739"
when deathdatetr >= "1740-00-00" and deathdatetr < "1750-00-00" then "1740 - 1749"
when deathdatetr >= "1750-00-00" and deathdatetr < "1760-00-00" then "1750 - 1759"
when deathdatetr >= "1760-00-00" and deathdatetr < "1770-00-00" then "1760 - 1769"
when deathdatetr >= "1770-00-00" and deathdatetr < "1780-00-00" then "1770 - 1779"
when deathdatetr >= "1780-00-00" and deathdatetr < "1790-00-00" then "1780 - 1789"
when deathdatetr >= "1790-00-00" and deathdatetr < "1800-00-00" then "1790 - 1799"
when deathdatetr >= "1800-00-00" and deathdatetr < "1810-00-00" then "1800 - 1809"
when deathdatetr >= "1810-00-00" and deathdatetr < "1820-00-00" then "1810 - 1819"
when deathdatetr >= "1820-00-00" and deathdatetr < "1830-00-00" then "1820 - 1829"
when deathdatetr >= "1830-00-00" and deathdatetr < "1840-00-00" then "1830 - 1839"
when deathdatetr >= "1840-00-00" and deathdatetr < "1850-00-00" then "1840 - 1849"
when deathdatetr >= "1850-00-00" and deathdatetr < "1860-00-00" then "1850 - 1859"
when deathdatetr >= "1860-00-00" and deathdatetr < "1870-00-00" then "1860 - 1869"
when deathdatetr >= "1870-00-00" and deathdatetr < "1880-00-00" then "1870 - 1879"
when deathdatetr >= "1880-00-00" and deathdatetr < "1890-00-00" then "1880 - 1889"
when deathdatetr >= "1890-00-00" and deathdatetr < "1900-00-00" then "1890 - 1899"
when deathdatetr >= "1900-00-00" and deathdatetr < "1910-00-00" then "1900 - 1909"
when deathdatetr >= "1910-00-00" and deathdatetr < "1920-00-00" then "1910 - 1919"
when deathdatetr >= "1920-00-00" and deathdatetr < "1930-00-00" then "1920 - 1929"
when deathdatetr >= "1930-00-00" and deathdatetr < "1940-00-00" then "1930 - 1939"
when deathdatetr >= "1940-00-00" and deathdatetr < "1950-00-00" then "1940 - 1949"
when deathdatetr >= "1950-00-00" then "1950 and later"
END AS Year,
COUNT(*) as Total
FROM tng_people
GROUP BY 1 ) y ON x.Year = y.Year
Then hit Save and Exit and click on the tick button next to the report name to test the result.
Note that this assumes the default naming of tables in TNG. If yours is different, then you will need to adjust the table name in the "FROM" line.
Example
This shows the first few years of a typical report.
# | Year | Total |
---|---|---|
1 | Bef. 1500 | 20 |
2 | 1500 - 1509 | 13 |
3 | 1510 - 1519 | 19 |
4 | 1520 - 1529 | 32 |
5 | 1530 - 1539 | 43 |
6 | 1540 - 1549 | 32 |
Customising the report
It's easy to use the ranges you want and/or count for a selected place or area.
- To adjust the ranges, you simply edit them and/or delete or insert lines.
Note that the ranges in the top part must correspond to the ranges in the bottom part:
Both the number of them and the text inside quotes must correspond exactly. - To add a specific place you add a
WHERE deathplace = "The Placename As Recorded"
between the FROM and GROUP BY lines at the very bottom. For example:
WHERE deathplace = "Salem, Massachusetts, USA" - To add an area you add a
WHERE deathplace LIKE "%A Common Placename As Recorded%"
between the FROM and GROUP BY lines at the very bottom. For example:
WHERE deathplace LIKE "%USA%"