A large query I created

This is for low user count systems, and low number of rows returned.
It returns information for financial planning aggregates.
It could be optimized a little more, I know.
Broken into 2 pieces, I could have cut this down a bit...first making a view, and then pulling from that...but then the summaries have to have blank text fields....so this might be simplest...seriously.


SELECT * FROM ((
SELECT IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = c.category_idAND cr.merger_id = '0'LIMIT 1 ), c.category_name ) category, IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = cc.category_idAND cr.merger_id = '0'LIMIT 1 ), cc.category_name ) subcategory, a.activity_name activity_name, m.ppp_abc, m.ppp_xyz, m.ppp_synergy, m.ppp_result, m.ppi_order, m.ppi_sale, m.ppi_cogs, m.ppi_rd, m.ppi_sm, m.ppi_admin, m.aap_action, m.aap_start, m.aap_completion, m.aap_manager, m.aap_status, m.api_order, m.api_sale, m.api_cogs, m.api_rd, m.api_sm, m.api_admin, (IFNULL( m.api_order, 0 ) - IFNULL( m.ppi_order, 0 ) ) AS fis_order, (IFNULL( m.api_sale, 0 ) - IFNULL( m.ppi_sale, 0 ) ) AS fis_sale, (IFNULL( m.api_cogs, 0 ) - IFNULL( m.ppi_cogs, 0 ) ) AS fis_cogs, (IFNULL( m.api_rd, 0 ) - IFNULL( m.ppi_rd, 0 ) ) AS fis_rd, (IFNULL( m.api_sm, 0 ) - IFNULL( m.ppi_sm, 0 ) ) AS fis_sm, (IFNULL( m.api_admin, 0 ) - IFNULL( m.ppi_admin, 0 ) ) AS fis_admin, a.activity_id activity_id, cc.category_id category_id, c.category_id subcategory_id, cc.parent_id, a.orderingFROM category ccLEFT JOIN category c ON cc.parent_id = c.category_idLEFT JOIN activity a ON a.category_id = cc.category_idLEFT JOIN `master` m ON m.activity_id = a.activity_idAND m.merger_id =0WHERE cc.parent_idIN ( 6, 0 ) ORDER BY cc.parent_id DESC , cc.category_id, a.ordering)UNION (
SELECT IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = cc.category_idAND cr.merger_id = '0'LIMIT 1 ), cc.category_name ) category, IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = c.category_idAND cr.merger_id = '0'LIMIT 1 ), c.category_name ) subcategory, 'Summary'activity_name, ''ppp_abc, ''ppp_xyz, ''ppp_synergy, ''ppp_result, SUM( ppi_order ) ppi_order, SUM( ppi_sale ) ppi_sale, SUM( ppi_cogs ) ppi_cogs, SUM( ppi_rd ) ppi_rd, SUM( ppi_sm ) ppi_sm, SUM( ppi_admin ) ppi_admin,'' aap_action,'' aap_start, ''aap_completion, ''aap_manager, ''aap_status, SUM( api_order ) api_order, SUM( api_sale ) api_sale, SUM( api_cogs ) api_cogs, SUM( api_rd ) api_rd, SUM( api_sm ) api_sm, SUM( api_admin ) api_admin, (SUM( api_order ) - SUM( ppi_order ) ) AS fis_order, (SUM( api_sale ) - SUM( ppi_sale ) ) AS fis_sale, (SUM( api_cogs ) - SUM( ppi_cogs ) ) AS fis_cogs, (SUM( api_rd ) - SUM( ppi_rd ) ) AS fis_rd, (SUM( api_sm ) - SUM( ppi_sm ) ) AS fis_sm, (SUM( api_admin ) - SUM( ppi_admin ) ) AS fis_admin, a.activity_id, c.category_id, c.parent_id, c.parent_id, 1000000000FROM `activity` aLEFT JOIN `master` m ON m.activity_id = a.activity_idLEFT JOIN category c ON a.category_id = c.category_idLEFT JOIN category cc ON cc.category_id = c.parent_idWHERE m.merger_id =0AND c.parent_id =6GROUP BY category, subcategory)UNION (SELECT IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = cc.category_idAND cr.merger_id = '0'LIMIT 1 ), cc.category_name ) category,
'Final Subtotal' subcategory, 'Summary'activity_name, ''ppp_abc, ''ppp_xyz, ''ppp_synergy, ''ppp_result, SUM( ppi_order ) ppi_order, SUM( ppi_sale ) ppi_sale, SUM( ppi_cogs ) ppi_cogs, SUM( ppi_rd ) ppi_rd, SUM( ppi_sm ) ppi_sm, SUM( ppi_admin ) ppi_admin,'' aap_action ,'' aap_start, ''aap_completion, ''aap_manager, ''aap_status, SUM( api_order ) api_order, SUM( api_sale ) api_sale, SUM( api_cogs ) api_cogs, SUM( api_rd ) api_rd, SUM( api_sm ) api_sm, SUM( api_admin ) api_admin, (SUM( api_order ) - SUM( ppi_order ) ) AS fis_order, (SUM( api_sale ) - SUM( ppi_sale ) ) AS fis_sale, (SUM( api_cogs ) - SUM( ppi_cogs ) ) AS fis_cogs, (SUM( api_rd ) - SUM( ppi_rd ) ) AS fis_rd, (SUM( api_sm ) - SUM( ppi_sm ) ) AS fis_sm, (SUM( api_admin ) - SUM( ppi_admin ) ) AS fis_admin, a.activity_id, c.category_id, c.parent_id, (c.parent_id - 0.5) parent_id, 1000000000FROM `activity` aLEFT JOIN `master` m ON m.activity_id = a.activity_idLEFT JOIN category c ON a.category_id = c.category_idLEFT JOIN category cc ON cc.category_id = c.parent_idWHERE m.merger_id =0AND c.parent_id =6GROUP BY category))combinedORDER BY parent_id DESC , category_id, ordering

How the city hurts your brain

http://www.boston.com/bostonglobe/ideas/articles/2009/01/04/how_the_city_hurts_your_brain/