|

Find that Charity

Charity registrations

Using data from Charity Commission for England and Wales, shows how many charities are registered and removed each year (first table).

The second table shows the number of charities registered in each year, and how many of those are still on the register after a number of years.

Owned by david, visibility: Public

SQL query
select registered.year, charities_registered, charities_removed from 
(select EXTRACT(YEAR from date_of_registration) as year, count(*) as charities_registered
from charity_ccewcharity
group by year) as registered 
	left outer join
    (select EXTRACT(YEAR from date_of_removal) as year, count(*) as charities_removed
from charity_ccewcharity
group by year) as removed
	on registered.year = removed.year
order by registered.year

64 rows

year charities_registered charities_removed
1960 1 - null -
1961 4,080 295
1962 18,699 100
1963 22,516 141
1964 19,830 138
1965 13,524 105
1966 8,563 111
1967 7,127 98
1968 4,895 92
1969 4,338 60
1970 3,497 80
1971 2,997 85
1972 3,033 125
1973 3,158 146
1974 3,258 148
1975 3,042 139
1976 3,103 121
1977 3,636 191
1978 3,578 161
1979 3,300 125
1980 4,034 181
1981 3,481 124
1982 3,802 154
1983 3,939 162
1984 4,095 169
1985 3,909 141
1986 4,184 162
1987 4,148 190
1988 4,170 270
1989 4,011 211
1990 4,121 154
1991 4,641 5,086
1992 5,087 11,989
1993 12,879 7,464
1994 12,016 4,561
1995 8,710 6,218
1996 9,777 9,959
1997 9,120 6,524
1998 6,346 4,386
1999 5,812 4,354
2000 5,636 9,889
2001 5,892 5,741
2002 5,464 4,609
2003 6,487 4,770
2004 6,575 5,612
2005 5,419 5,057
2006 4,976 4,594
2007 4,945 4,899
2008 5,401 6,433
2009 6,239 14,304
2010 6,347 6,281
2011 5,739 7,138
2012 5,436 4,570
2013 4,993 4,510
2014 4,663 4,249
2015 5,336 4,567
2016 5,973 4,302
2017 5,592 4,707
2018 4,945 5,352
2019 5,753 6,058
2020 5,721 3,981
2021 4,315 3,964
2022 4,021 5,914
2023 890 1,222
Copy and export data

Duration: 280.63ms

SQL query
select EXTRACT(YEAR from date_of_registration) as "Year of registration",
	COUNT(*) as "Charities registered",
    count(*) filter (where date_of_removal is null) as "Charities still active",
	count(*) Filter (where EXTRACT(YEAR from date_of_removal) - EXTRACT(YEAR from date_of_registration) <= 2) as "Removed after 2 years or less",
	count(*) Filter (where EXTRACT(YEAR from date_of_removal) - EXTRACT(YEAR from date_of_registration) between 3 and 5) as "Removed after between 3 and 5 years",
	count(*) Filter (where EXTRACT(YEAR from date_of_removal) - EXTRACT(YEAR from date_of_registration) between 6 and 10) as "Removed after between 6 and 10 years",
	count(*) Filter (where EXTRACT(YEAR from date_of_removal) - EXTRACT(YEAR from date_of_registration) between 11 and 25) as "Removed after between 11 and 25 years",
	count(*) Filter (where EXTRACT(YEAR from date_of_removal) - EXTRACT(YEAR from date_of_registration) > 25) as "Removed after more than 25 years"
from charity_ccewcharity
group by "Year of registration"
order by "Year of registration"

64 rows

Year of registration Charities registered Charities still active Removed after 2 years or less Removed after between 3 and 5 years Removed after between 6 and 10 years Removed after between 11 and 25 years Removed after more than 25 years
1960 1 1 0 0 0 0 0
1961 4,080 942 299 2 5 36 2,796
1962 18,699 6,436 108 23 38 153 11,941
1963 22,516 7,791 147 32 80 259 14,207
1964 19,830 6,258 142 25 88 224 13,093
1965 13,524 3,556 84 26 60 147 9,651
1966 8,563 2,321 94 16 52 345 5,735
1967 7,127 2,239 74 23 42 794 3,955
1968 4,895 1,478 43 22 40 696 2,616
1969 4,338 1,409 42 25 57 807 1,998
1970 3,497 1,210 27 20 44 748 1,448
1971 2,997 1,107 17 14 30 713 1,116
1972 3,033 1,048 25 26 30 863 1,041
1973 3,158 1,114 21 23 16 876 1,108
1974 3,258 1,125 34 24 33 1,049 993
1975 3,042 1,069 20 21 30 1,025 877
1976 3,103 1,132 7 17 24 1,149 774
1977 3,636 1,315 17 23 30 1,321 930
1978 3,578 1,381 19 23 26 1,247 882
1979 3,300 1,255 18 24 44 1,122 837
1980 4,034 1,559 16 22 48 1,479 910
1981 3,481 1,260 15 19 96 1,305 786
1982 3,802 1,411 25 14 264 1,310 778
1983 3,939 1,426 23 22 316 1,318 834
1984 4,095 1,481 15 34 420 1,520 625
1985 3,909 1,427 14 21 477 1,422 548
1986 4,184 1,529 16 75 597 1,478 489
1987 4,148 1,466 19 159 545 1,429 530
1988 4,170 1,634 12 205 495 1,362 462
1989 4,011 1,518 45 207 505 1,334 402
1990 4,121 1,600 79 154 631 1,284 373
1991 4,641 1,721 292 283 633 1,304 408
1992 5,087 1,963 479 333 594 1,419 299
1993 12,879 4,483 340 566 1,552 5,111 827
1994 12,016 4,468 306 611 1,820 4,266 545
1995 8,710 3,661 325 548 1,082 2,829 265
1996 9,777 3,759 326 787 1,557 3,123 225
1997 9,120 3,372 194 668 1,292 3,562 32
1998 6,346 2,831 130 389 830 2,166 0
1999 5,812 2,828 129 383 871 1,601 0
2000 5,636 2,625 123 407 1,105 1,376 0
2001 5,892 3,002 148 370 1,188 1,184 0
2002 5,464 2,898 169 344 1,031 1,022 0
2003 6,487 3,410 168 478 1,087 1,344 0
2004 6,575 3,728 176 627 917 1,127 0
2005 5,419 3,128 160 522 767 842 0
2006 4,976 3,083 124 498 586 685 0
2007 4,945 3,037 208 458 659 583 0
2008 5,401 3,515 200 475 665 546 0
2009 6,239 4,730 180 383 644 302 0
2010 6,347 4,666 281 476 701 223 0
2011 5,739 4,135 219 478 749 158 0
2012 5,436 4,039 204 467 696 30 0
2013 4,993 3,911 164 428 490 0 0
2014 4,663 3,888 118 348 309 0 0
2015 5,336 4,664 102 325 245 0 0
2016 5,973 5,358 119 342 154 0 0
2017 5,592 5,151 104 318 19 0 0
2018 4,945 4,701 68 176 0 0 0
2019 5,753 5,540 100 113 0 0 0
2020 5,721 5,597 104 20 0 0 0
2021 4,315 4,275 40 0 0 0 0
2022 4,021 4,017 4 0 0 0 0
2023 890 890 0 0 0 0 0
Copy and export data

Duration: 304.48ms