| |

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
1,136,855 CARDIFF UNIVERSITY 2021-07-31 236,852,128 41,662,372 195,189,756 604,593,000
1,079,327 CHANGE, GROW, LIVE 2021-03-31 220,126,000 219,729,000 397,000 220,126,000
216,250 BARNARDO'S 2021-03-31 217,712,750 167,838,750 49,874,000 280,464,000
209,131 THE BRITISH COUNCIL 2021-03-31 213,083,466 30,897,623 182,185,843 924,965,472
222,377 ROYAL MENCAP SOCIETY 2022-03-31 200,656,902 187,338,127 13,318,775 239,014,000
234,887 TURNING POINT 2021-03-31 132,795,548 130,812,414 1,983,134 132,812,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 2021-03-31 96,391,000 94,569,000 1,822,000 96,901,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 2021-03-31 78,380,918 76,475,000 1,905,918 95,457,000
215,174 THE SALVATION ARMY SOCIAL WORK TRUST 2021-03-31 76,790,000 69,115,000 7,675,000 172,706,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
515,755 HUMANKIND CHARITY 2022-03-31 69,877,506 65,787,686 4,089,820 70,852,215
207,994 MARIE CURIE 2021-03-31 69,346,000 46,646,000 22,700,000 170,616,000
1,146,792 CANAL & RIVER TRUST 2021-03-31 68,878,954 12,478,954 56,400,000 215,400,000
313,069 HF TRUST LIMITED 2021-03-31 67,797,217 65,302,217 2,495,000 84,985,000
220,949 THE BRITISH RED CROSS SOCIETY 2021-12-31 66,447,619 37,200,000 29,247,619 226,500,000
Copy and export data

Duration: 1486.50ms

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 9418314 2158083 219718240 2,153 28 109 130 7
02 under-10k 1979124 10304523 500716865 15,344 119 1,351 1,438 32
03 10k-to-25k 1755673 13742252 566651325 18,061 203 1,867 2,014 56
04 25k-to-50k 4769493 20958508 653246368 13,391 299 1,784 2,002 81
05 50k-to-100k 24616980 51908660 1183669859 13,037 578 2,047 2,480 145
06 100k-to-250k 89857634 342011416 3031444658 14,757 1,068 2,913 3,623 358
07 250k-to-500k 96750285 151943326 2695796495 7,014 803 1,814 2,248 369
08 500k-to-1m 209592682 229206585 3598978461 4,607 877 1,434 1,849 462
09 1m-to-5m 1124108631 753415891 11504437603 5,100 1,339 1,777 2,378 738
10 5m-to-10m 797510906 397761161 7285549510 1,042 305 376 512 169
11 over-10m 3706605910 1708550447 29460731678 1,184 359 430 574 215
12 over-100m 2045033527 1767617684 21046518954 95 50 53 67 36
Copy and export data

Duration: 290.30ms

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"