Spreadsheet: 804501d1668104659-sumifs-countifs-jd
Sheet:  Query

 | SELECT
 | [NAME],
 | c.evnt_fscl_yr,
 | TRIM(c.EVNT_NM) AS EVNT_NM
 | ,[NAME].EVNT_NM),LENGTH(TRIM(c.EVNT_NM)) - STRPOS(TRIM(c.EVNT_NM)," ")) AS Short_Name
 | ,[NAME]PROD_LVL_NBR
 | ,[NAME].[NAME]
 | ,[NAME].SVP_DESC
 | ,rtrim(case when substr(sku.SUB_DEPT_NBR, 1,2) = '00' then substr(sku.SUB_DEPT_NBR,3) else substr(sku.SUB_DEPT_NBR,2) end) AS SUB_DEPT_NBR
 | ,sku.SUB_DEPT_DESC
 | ,sku.CLASS_NBR
 | ,sku.CLASS_DESC
 | ,((SUM(ads.GROSS_SLS_AMT)+ SUM(ads.STR_MKDN_AMT)+ SUM(ads.BYR_MKDN_AMT)) / SUM(ads.GROSS_UNT_SLS))  AS Avg_Normal_Price
 | ,(SUM(ads.GROSS_SLS_AMT) + SUM(ads.STR_MKDN_AMT)) /SUM(ads.GROSS_UNT_SLS) AS Avg_Sale_Price
 | ,SUM(ads.GROSS_SLS_AMT) AS Gross_Sales
 | ,SUM(ads.GROSS_UNT_SLS) AS Gross_Units
 | ,((SUM(ads.GROSS_SLS_AMT)+ SUM(ads.STR_MKDN_AMT)+ SUM(ads.BYR_MKDN_AMT)) / SUM(ads.GROSS_UNT_SLS)) - ((SUM(ads.GROSS_SLS_AMT) + SUM(ads.STR_MKDN_AMT)) /SUM(ads.GROSS_UNT_SLS)) AS Discount_Dollar
 | ,SUM(ads.BYR_MKDN_AMT)/(SUM(ads.GROSS_SLS_AMT) + SUM(ads.STR_MKDN_AMT) + SUM(ads.BYR_MKDN_AMT)) As Discount_Percent
 | FROM `pr-edw-views-thd.FIN_EVENT_ANALYTICS.PBE_EVNT_STR_ITEM_D` AS c
 | LEFT JOIN `pr-edw-views-thd.SLS.SKU_STR_DAY_SLS_ADS` as ads ON
 | [NAME] =  ads.STR_NBR
 | AND SAFE_CAST(c.PROD_LVL_NBR AS INT64) = ads.SKU_NBR
 | AND [NAME] = ads.SKU_CRT_DT
 | AND ADS.SLS_DT = [NAME]
 | LEFT JOIN `pr-edw-views-thd.SHARED.SKU_HIER_FD` as sku ON
 | SAFE_CAST(c.PROD_LVL_NBR AS INT64) = [NAME].SKU_NBR
 | AND [NAME] = sku.[NAME]
 | WHERE [NAME] IN (11229,11227,11237,11231,11232,11234,11233,11238,11243,11246,11244,11242,11245,11247,11248,11250,11249,11252,11253,11254,11255,11256,11258,11261,11260,11263,11265,11266,11264,11267,11270,11272,11271,11273,11279,35684,35686,35685,35690,35692,35693,35695,35698,35699,35696,35704,35703,35701,35705,35708,35721,35709,35710,35716,35724,35722,35723,35726,35729,35731,35728,35736,35737,35738,35743,35741,35744,35749,35747,35751,35750,35755,35759,35761,35764,35763,35769,35766,35767,35777,35779,35782,35783,35784,35788,35787,35786,35791,35789,35795,35793,35798,35804,35802,35805,35803,35811,35809,35806,35808,35815,35818,35822,35817,35825,35850,35848,35849,35851,35852,35857,35855,35859,35867,35870,35872,35877,35880,35885,35891,35889,35893,35896,35898,35897,35899,35906,35902,35900,35901,35912,35911,35916,35913,35915,35917,35919,35918,35920,35921,35925,35926,35930,35927,35932,35937,35936)
 | --AND c.PROD_LVL_NBR IN ('[PHONE]','[PHONE]','[PHONE]')
 | AND [NAME] Between c.EVNT_EFF_BGN_DT AND c.EVNT_EFF_END_DT
 | AND c.CORE_MER_FLG = 'C'
 | AND ads.GROSS_UNT_SLS  > 0
 | --AND c.RETL_AMT > 0
 | GROUP BY 1,2,3,4,5,6,7,8,9,10,11
 | ORDER BY [NAME] ASC, Discount_Percent DESC
 | [NAME] Used this one
 | SELECT
 | [NAME],
 | c.evnt_fscl_yr,
 | TRIM(c.EVNT_NM) AS EVNT_NM
 | ,[NAME].EVNT_NM),LENGTH(TRIM(c.EVNT_NM)) - STRPOS(TRIM(c.EVNT_NM)," ")) AS Short_Name
 | ,[NAME]PROD_LVL_NBR
 | ,[NAME].[NAME]
 | ,[NAME].SVP_DESC
 | ,rtrim(case when substr(sku.SUB_DEPT_NBR, 1,2) = '00' then substr(sku.SUB_DEPT_NBR,3) else substr(sku.SUB_DEPT_NBR,2) end) AS SUB_DEPT_NBR
 | ,sku.SUB_DEPT_DESC
 | ,sku.CLASS_NBR
 | ,sku.CLASS_DESC
 | ,((SUM(ads.GROSS_SLS_AMT)+ SUM(ads.STR_MKDN_AMT)+ SUM(ads.BYR_MKDN_AMT)) / SUM(ads.GROSS_UNT_SLS))  AS Avg_Normal_Price
 | ,(SUM(ads.GROSS_SLS_AMT) + SUM(ads.STR_MKDN_AMT)) /SUM(ads.GROSS_UNT_SLS) AS Avg_Sale_Price
 | ,SUM(ads.GROSS_SLS_AMT) AS Gross_Sales
 | ,SUM(ads.GROSS_UNT_SLS) AS Gross_Units
 | ,((SUM(ads.GROSS_SLS_AMT)+ SUM(ads.STR_MKDN_AMT)+ SUM(ads.BYR_MKDN_AMT)) / SUM(ads.GROSS_UNT_SLS)) - ((SUM(ads.GROSS_SLS_AMT) + SUM(ads.STR_MKDN_AMT)) /SUM(ads.GROSS_UNT_SLS)) AS Discount_Dollar
 | ,SUM(ads.BYR_MKDN_AMT)/(SUM(ads.GROSS_SLS_AMT) + SUM(ads.STR_MKDN_AMT) + SUM(ads.BYR_MKDN_AMT)) As Discount_Percent
 | FROM `pr-edw-views-thd.FIN_EVENT_ANALYTICS.PBE_EVNT_STR_ITEM_D` AS c
 | LEFT JOIN `pr-edw-views-thd.SLS.SKU_STR_DAY_SLS_ADS` as ads ON
 | [NAME] =  ads.STR_NBR
 | AND SAFE_CAST(c.PROD_LVL_NBR AS INT64) = ads.SKU_NBR
 | AND [NAME] = ads.SKU_CRT_DT
 | AND ADS.SLS_DT = [NAME]
 | LEFT JOIN `pr-edw-views-thd.SHARED.SKU_HIER_FD` as sku ON
 | SAFE_CAST(c.PROD_LVL_NBR AS INT64) = [NAME].SKU_NBR
 | AND [NAME] = sku.[NAME]
 | WHERE [NAME] IN (11229,11227,11237,11231,11232,11234,11233,11238,11243,11246,11244,11242,11245,11247,11248,11250,11249,11252,11253,11254,11255,11256,11258,11261,11260,11263,11265,11266,11264,11267,11270,11272,11271,11273,11279,35684,35686,35685,35690,35692,35693,35695,35698,35699,35696,35704,35703,35701,35705,35708,35721,35709,35710,35716,35724,35722,35723,35726,35729,35731,35728,35736,35737,35738,35743,35741,35744,35749,35747,35751,35750,35755,35759,35761,35764,35763,35769,35766,35767,35777,35779,35782,35783,35784,35788,35787,35786,35791,35789,35795,35793,35798,35804,35802,35805,35803,35811,35809,35806,35808,35815,35818,35822,35817,35825,35850,35848,35849,35851,35852,35857,35855,35859,35867,35870,35872,35877,35880,35885,35891,35889,35893,35896,35898,35897,35899,35906,35902,35900,35901,35912,35911,35916,35913,35915,35917,35919,35918,35920,35921,35925,35926,35930,35927,35932,35937,35936,35935,35934,35939,35940,35938,35943,35947,35955,35958,35959,35965,35969,35970,35971,35972,35974,35978,35977,35976,35975,35979,35982,35980,35985,35988,35989,35991,35981,35990,35992,35994,35993,35995,35996,35997,36000,36001,36005,36007)
 | --AND c.PROD_LVL_NBR IN ('[PHONE]','[PHONE]','[PHONE]')
 | AND [NAME] Between c.EVNT_EFF_BGN_DT AND c.EVNT_EFF_END_DT
 | AND c.cal_dt <= '2022-11-08'
 | AND c.CORE_MER_FLG = 'C'
 | AND ads.GROSS_UNT_SLS  > 0
 | --AND c.RETL_AMT > 0
 | GROUP BY 1,2,3,4,5,6,7,8,9,10,11
 | ORDER BY [NAME] ASC, Discount_Percent DESC