|

Find that Charity

Income from Government

This query shows the charities in England and Wales with the largest income from government in their latest financial year, according to their data returned to the Charity Commission for England and Wales.

Owned by david, visibility: Public

SQL query
select c.registered_charity_number as "Charity Number",
	c.charity_name as "Name",
    latest_acc_fin_period_end_date as "Financial Year End",
    income_from_government_grants + income_from_government_contracts as "Income from Government",
    income_from_government_contracts as "Income from Government Contracts",
    income_from_government_grants as "Income from Government Grants",
    total_gross_income as "Total income"
from charity_ccewcharityarparta parta
	inner join charity_ccewcharity c
    	on c.organisation_number = parta.organisation_number
        	and c.latest_acc_fin_period_end_date = parta.fin_period_end_date
order by "Income from Government" desc nulls last
limit 20

20 rows

Charity Number Name Financial Year End Income from Government Income from Government Contracts Income from Government Grants Total income
209,131 THE BRITISH COUNCIL 2022-03-31 280,752,160 74,100,293 206,651,867 896,655,974
1,136,855 CARDIFF UNIVERSITY 2021-07-31 236,852,128 41,662,372 195,189,756 604,593,000
222,377 ROYAL MENCAP SOCIETY 2022-03-31 200,656,902 187,338,127 13,318,775 239,014,000
216,250 BARNARDO'S 2022-03-31 182,335,546 164,600,546 17,735,000 299,503,000
234,887 TURNING POINT 2022-03-31 143,659,568 139,037,312 4,622,256 143,694,000
326,550 SUSTRANS 2022-03-31 106,784,784 7,914,784 98,870,000 114,715,000
279,057 THE NATIONAL ASSOCIATION OF CITIZENS ADVICE BUREAUX 2022-03-31 99,180,000 30,000 99,150,000 153,624,000
1,091,318 HCT GROUP 2020-09-30 97,310,602 96,054,793 1,255,809 123,706,829
265,249 UNITED RESPONSE 2022-03-31 96,234,000 94,575,000 1,659,000 96,970,000
215,174 THE SALVATION ARMY SOCIAL WORK TRUST 2022-03-31 93,079,000 86,588,000 6,491,000 201,362,000
1,097,940 ACTION FOR CHILDREN 2022-03-31 86,616,000 86,502,000 114,000 142,648,000
1,052,183 ANCHOR TRUST 2018-03-31 82,210,950 82,149,000 61,950 331,837,000
269,425 THE NATIONAL AUTISTIC SOCIETY 2022-03-31 77,278,392 75,559,000 1,719,392 95,633,000
1,048,355 THE ORDERS OF ST JOHN CARE TRUST 2022-03-31 73,853,347 66,980,676 6,872,671 142,814,877
1,090,163 THERA TRUST 2022-03-31 70,188,954 67,493,076 2,695,878 78,536,590
515,755 HUMANKIND CHARITY 2022-03-31 69,877,506 65,787,686 4,089,820 70,852,215
1,140,062 THE FRANCIS CRICK INSTITUTE LIMITED 2022-03-31 69,554,164 1,249,866 68,304,298 185,630,048
220,949 THE BRITISH RED CROSS SOCIETY 2021-12-31 66,447,619 37,200,000 29,247,619 226,500,000
1,156,486 Choice Support 2022-03-31 64,836,000 64,352,000 484,000 65,394,000
1,175,080 PARTNERSHIP SUPPORT GROUP 2022-03-31 64,836,000 64,352,000 484,000 65,394,000
Copy and export data

Duration: 411.67ms

SQL query 
select c.charity_name as "chartjs_barh_label",
    income_from_government_grants + income_from_government_contracts as "chartjs_barh_value"
from charity_ccewcharityarparta parta
	inner join charity_ccewcharity c
    	on c.organisation_number = parta.organisation_number
        	and c.latest_acc_fin_period_end_date = parta.fin_period_end_date
order by "chartjs_barh_value" desc nulls last
limit 20
SQL query
select case when latest_income > 100000000 then '12 over-100m'
		when latest_income > 10000000 then '11 over-10m'
		when latest_income > 5000000 then '10 5m-to-10m'
		when latest_income > 1000000 then '09 1m-to-5m'
		when latest_income > 500000 then '08 500k-to-1m'
		when latest_income > 250000 then '07 250k-to-500k'
		when latest_income > 100000 then '06 100k-to-250k'
		when latest_income > 50000 then '05 50k-to-100k'
		when latest_income > 25000 then '04 25k-to-50k'
		when latest_income > 10000 then '03 10k-to-25k'
		when latest_income > 0 then '02 under-10k'
		when latest_income = 0 then '01 zero-income'
		else '00 unknown' end as "Income band",
	sum(cc_parta.income_from_government_contracts) as "government_contracts",
	sum(cc_parta.income_from_government_grants) as "government_grants",
	sum(cc_parta.total_gross_income) as "total_income",
	count(*) as "number_of_charities",
	sum(case when cc_parta.income_from_government_contracts > 0 then 1 else 0 end) as "has_government_contracts",
	sum(case when cc_parta.income_from_government_grants > 0 then 1 else 0 end) as "has_government_grants",
	sum(case when (coalesce(cc_parta.income_from_government_grants,0) + coalesce(cc_parta.income_from_government_contracts,0)) > 0 then 1 else 0 end) as "has_government",
	sum(case when cc_parta.income_from_government_grants > 0 and cc_parta.income_from_government_contracts > 0 then 1 else 0 end) as "has_government_both"
from charity_ccewcharity cc 
	inner join charity_ccewcharityarparta cc_parta
		on cc.registered_charity_number = cc_parta.registered_charity_number 
where charity_registration_status = 'Registered'
	and linked_charity_number = 0
	and fin_period_end_date >= '2019-04-01'
	and fin_period_end_date <= '2020-03-31'
group by "Income band"
order by "Income band"

12 rows

Income band government_contracts government_grants total_income number_of_charities has_government_contracts has_government_grants has_government has_government_both
01 zero-income 14166705 8252494 294641243 2,150 39 130 154 15
02 under-10k 2524002 11323946 455151376 14,379 119 1,313 1,400 32
03 10k-to-25k 1325473 14198573 564839685 18,388 199 1,892 2,033 58
04 25k-to-50k 4645141 20588733 649546258 13,277 283 1,746 1,950 79
05 50k-to-100k 33811154 49747744 1148920546 13,006 573 2,001 2,438 136
06 100k-to-250k 80042453 336772117 2986299587 14,879 1,057 2,888 3,585 360
07 250k-to-500k 93423329 147277308 2660433853 7,075 763 1,777 2,195 345
08 500k-to-1m 206106078 220400543 3547800983 4,619 871 1,431 1,845 457
09 1m-to-5m 1082471049 754714172 11236850422 5,191 1,350 1,788 2,401 737
10 5m-to-10m 784923751 408827081 7095735192 1,068 311 389 527 173
11 over-10m 3774308103 1681981955 29834491103 1,250 384 458 609 233
12 over-100m 2044995707 1780997684 21207278352 97 49 55 68 36
Copy and export data

Duration: 196.07ms

SQL query 
select case when latest_income > 100000000 then '12 over-100m'
		when latest_income > 10000000 then '11 over-10m'
		when latest_income > 5000000 then '10 5m-to-10m'
		when latest_income > 1000000 then '09 1m-to-5m'
		when latest_income > 500000 then '08 500k-to-1m'
		when latest_income > 250000 then '07 250k-to-500k'
		when latest_income > 100000 then '06 100k-to-250k'
		when latest_income > 50000 then '05 50k-to-100k'
		when latest_income > 25000 then '04 25k-to-50k'
		when latest_income > 10000 then '03 10k-to-25k'
		when latest_income > 0 then '02 under-10k'
		when latest_income = 0 then '01 zero-income'
		else '00 unknown' end as "chartjs_bar_label",
        coalesce (sum(cc_parta.income_from_government_contracts + cc_parta.income_from_government_grants) / sum(cc_parta.total_gross_income), 0) * 100  as "chartjs_bar_value"
from charity_ccewcharity cc 
	inner join charity_ccewcharityarparta cc_parta
		on cc.registered_charity_number = cc_parta.registered_charity_number 
           and cc.latest_acc_fin_period_end_date = cc_parta.fin_period_end_date 
where linked_charity_number = 0
	and fin_period_end_date >= '2019-04-01'
	and fin_period_end_date <= '2020-03-31'
group by "chartjs_bar_label"
order by "chartjs_bar_label"