Community Platform

You must log on first.

OK: DROP TABLE IF EXISTS lookup.sumReport2013b;


OK: CREATE TABLE lookup.sumReport2013b SELECT STATE, CASE WHEN SUBSECCD = '03' AND fndncd IN ('02','03') THEN 'PF-Oper' WHEN SUBSECCD = '03' AND fndncd IN ('04') THEN 'PF-Grnt' WHEN SUBSECCD = '03' AND fndncd NOT IN ('02','03','04') AND cfiler='Y' and cZfiler = 'N' THEN CONCAT('PC-', level2) WHEN SUBSECCD = '03' AND fndncd NOT IN ('02','03','04') AND NOT (cfiler='Y' and cZfiler = 'N') THEN CONCAT('PC-NonFlr') WHEN SUBSECCD = '03' THEN 'c3-unmarked (should=0)' WHEN SUBSECCD = '04' THEN 'c4-civ.leagues' WHEN SUBSECCD = '05' THEN 'c5-unions' WHEN SUBSECCD = '06' THEN 'c6-bus leagues' WHEN SUBSECCD = '07' THEN 'c7-soc clubs' WHEN SUBSECCD = '06' THEN 'c6-bus leagues' WHEN SUBSECCD IN ('08','10') THEN 'c8/10-frat benef' WHEN SUBSECCD = '19' THEN 'c19-vets' ELSE 'other' END AS grp, COUNT(*) AS COUNT, SUM( if( cfiler='Y' and cZfiler = 'N', cAssets, 0)) AS assets, SUM( if( cfiler='Y' and cZfiler = 'N', cTotRev, 0)) AS grRec, '2013' AS year FROM bmf.bm1310 WHERE outnccs <>'OUT' GROUP BY state, grp;


OK: INSERT INTO lookup.sumReport2013b (state, grp, count, assets, grRec, year) SELECT STATE, CASE WHEN SUBSECCD = '03' AND fndncd IN ('02','03') THEN 'PF-Oper' WHEN SUBSECCD = '03' AND fndncd IN ('04') THEN 'PF-Grnt' WHEN SUBSECCD = '03' AND fndncd NOT IN ('02','03','04') AND cfiler='Y' and cZfiler = 'N' THEN CONCAT('PC-', level2) WHEN SUBSECCD = '03' AND fndncd NOT IN ('02','03','04') AND NOT (cfiler='Y' and cZfiler = 'N') THEN CONCAT('PC-NonFlr') WHEN SUBSECCD = '04' THEN 'c4-civ.leagues' WHEN SUBSECCD = '05' THEN 'c5-unions' WHEN SUBSECCD = '06' THEN 'c6-bus leagues' WHEN SUBSECCD = '07' THEN 'c7-soc clubs' WHEN SUBSECCD = '06' THEN 'c6-bus leagues' WHEN SUBSECCD IN ('08','10') THEN 'c8/10-frat benef' WHEN SUBSECCD = '19' THEN 'c19-vets' ELSE 'other' END AS grp, COUNT(*) AS COUNT, SUM( if( cfiler='Y' and cZfiler = 'N', cAssets, 0)) AS assets, SUM( if( cfiler='Y' and cZfiler = 'N', cTotRev, 0)) AS grRec, '2003' AS year FROM bmf.bm0311 WHERE outnccs <>'OUT' GROUP BY state, grp;

- Num rows affected: 663 -

OK: INSERT INTO lookup.sumReport2013b (state, grp, year, count, assets, grRec) SELECT 'US' AS STATE, grp, year, SUM(count) AS COUNT, SUM( assets) AS assets, SUM( grRec ) AS grRec FROM lookup.sumReport2013b WHERE state <>'US' GROUP BY grp, year;

- Num rows affected: 26 -
Query:

INSERT INTO lookup.sumReport2013b (state, grp, count,year) SELECT state, 'CONG' AS grp, NumCongs AS COUNT, '____' AS year FROM lookup.congregations;

Table 'lookup.congregations' doesn't exist

- Num rows affected: -1 -

Finished!