Generated query from TUTD

gistfile1.sql

DROP PROCEDURE IF EXISTS sp_create_and_display_exposures_table;

DELIMITER //

CREATE PROCEDURE sp_create_and_display_exposures_table() BEGIN

DROP TABLE IF EXISTS actlzd_vw_total_exposures_all_years;

CREATE TABLE actlzd_vw_total_exposures_all_years AS

SELECT IFNULL(`ctm`.`field_movie_release_year_value`, 'all_years') AS `year`,
(
    SELECT `vma`.`total`
    FROM `vw_movies_all` `vma`
    WHERE `vma`.`year` = `ctm`.`field_movie_release_year_value`
) AS `total_movies_reviewed`,
(
    SELECT `vtmwbey`.`total_movies`
    FROM (
        SELECT IFNULL(`ctm`.`field_movie_release_year_value`, 'all_years') AS `year`,
        COUNT(DISTINCT `cfatbs`.`vid`) AS `total_movies`
        FROM `content_type_movie` `ctm`
        LEFT JOIN `content_field_all_tobacco_brands_shown` `cfatbs` ON `ctm`.`vid` = `cfatbs`.`vid`
        WHERE `cfatbs`.`field_all_tobacco_brands_shown_value` IS NOT NULL
        GROUP BY `ctm`.`field_movie_release_year_value` WITH ROLLUP
    ) `vtmwbey`
    WHERE `vtmwbey`.`year` = `ctm`.`field_movie_release_year_value`
) AS `total_movies_with_brand_exposures`,
CONCAT(
    ROUND(
        (
            (
                (
                    SELECT `vtmwbey`.`total_movies`
                    FROM (
                        SELECT IFNULL(`ctm`.`field_movie_release_year_value`, 'all_years') AS `year`,
                        COUNT(DISTINCT `cfatbs`.`vid`) AS `total_movies`
                        FROM `content_type_movie` `ctm`
                        LEFT JOIN `content_field_all_tobacco_brands_shown` `cfatbs` ON `ctm`.`vid` = `cfatbs`.`vid`
                        WHERE `cfatbs`.`field_all_tobacco_brands_shown_value` IS NOT NULL
                        GROUP BY `ctm`.`field_movie_release_year_value` WITH ROLLUP
                    ) `vtmwbey`
                    WHERE `vtmwbey`.`year` = `ctm`.`field_movie_release_year_value`
                ) / (
                    SELECT `vma`.`total`
                    FROM `vw_movies_all` `vma`
                    WHERE `vma`.`year` = `ctm`.`field_movie_release_year_value`
                )
            ) * 100
        ), 2
    ),'%'
) AS `percentage_of_all_movies`,
(
    SELECT `vtbey`.`total_brand_exposures`
    FROM (
        SELECT IFNULL(`ctm`.`field_movie_release_year_value`,'all_years') AS `year`,
        COUNT(`cfatbs`.`field_all_tobacco_brands_shown_value`) AS 'total_brand_exposures'
        FROM `content_type_movie` `ctm`
        LEFT JOIN `content_field_all_tobacco_brands_shown` `cfatbs` ON `cfatbs`.`vid` = `ctm`.`vid`
        WHERE `cfatbs`.`field_all_tobacco_brands_shown_value` IS NOT NULL
        GROUP BY `ctm`.`field_movie_release_year_value` WITH ROLLUP
    ) `vtbey`
    WHERE `vtbey`.`year` = `ctm`.`field_movie_release_year_value`
) AS `total_exposures_all_brands`,
IFNULL(`cfatbs`.`field_all_tobacco_brands_shown_value`,'all_brands') AS `tobacco_brand`,
COUNT(0) AS `total_exposures`,
CONCAT(
    ROUND(
        (
            (
                (COUNT(0) / (
                    SELECT `vtbey`.`total_brand_exposures`
                    FROM (
                        SELECT IFNULL(`ctm`.`field_movie_release_year_value`,'all_years') AS `year`, 
                        COUNT(`cfatbs`.`field_all_tobacco_brands_shown_value`) AS 'total_brand_exposures'
                        FROM `content_type_movie` `ctm`
                        LEFT JOIN `content_field_all_tobacco_brands_shown` `cfatbs` ON `cfatbs`.`vid` = `ctm`.`vid`
                        WHERE `cfatbs`.`field_all_tobacco_brands_shown_value` IS NOT NULL
                        GROUP BY `ctm`.`field_movie_release_year_value` WITH ROLLUP
                    ) `vtbey`
                    WHERE `vtbey`.`year` = `ctm`.`field_movie_release_year_value`)
                )
            ) * 100
        ), 1
    ), '%'
) AS `percent_of_total_exposures`
FROM `content_field_all_tobacco_brands_shown` `cfatbs` 
LEFT JOIN `content_type_movie` `ctm` ON `cfatbs`.`vid` = `ctm`.`vid` 
WHERE `cfatbs`.`field_all_tobacco_brands_shown_value` IS NOT NULL 
GROUP BY `ctm`.`field_movie_release_year_value`, `cfatbs`.`field_all_tobacco_brands_shown_value` 
WITH ROLLUP; 

SET @total_movies_reviewed_all_years = (
    SELECT SUM(total_movies_reviewed) 
    FROM `actlzd_vw_total_exposures_all_years` 
    WHERE year != 'all_years' 
    AND tobacco_brand = 'all_brands'
); 

SET @total_movies_with_brand_exposures_all_years = (
    SELECT SUM(total_movies_with_brand_exposures) 
    FROM `actlzd_vw_total_exposures_all_years` 
    WHERE year != 'all_years' AND tobacco_brand = 'all_brands'
); 

SET @total_exposures_all_brands_all_years = (
    SELECT SUM(total_exposures_all_brands) FROM `actlzd_vw_total_exposures_all_years` 
    WHERE year != 'all_years' 
    AND tobacco_brand = 'all_brands'
); 

UPDATE `actlzd_vw_total_exposures_all_years` 
SET `actlzd_vw_total_exposures_all_years`.`total_movies_reviewed` = @total_movies_reviewed_all_years, 
`actlzd_vw_total_exposures_all_years`.`total_movies_with_brand_exposures` = @total_movies_with_brand_exposures_all_years, 
`actlzd_vw_total_exposures_all_years`.`total_exposures_all_brands` = @total_exposures_all_brands_all_years, 
`actlzd_vw_total_exposures_all_years`.`percent_of_total_exposures` = '100.0%' 
WHERE `actlzd_vw_total_exposures_all_years`.`YEAR` = 'all_years';

SELECT * FROM `actlzd_vw_total_exposures_all_years`; 

END// 
DELIMITER ;

More parentheses than Lisp! How is that possible?

question: is this table actlzd_vw_total_exposures_all_years used outside the scope of this procedure? if not, then perhaps creating it as temp table may be advisable.