SELECT
	ROW_NUMBER() OVER(ORDER BY (ROUND((
          (
              ((((SUM(stl.REALACTUB_STL) + IF(TB_UB.TOT_QTYTD IS NOT NULL, TB_UB.TOT_QTYTD, 0)) / 336) * 100) * (TB_UB.PERCENTAGE_AC / 100)) +
              ((((SUM(stl.REALACTPS_STL) + IF(TB_PS.TOT_QTYTD IS NOT NULL, TB_PS.TOT_QTYTD, 0)) / 21000) * 100) * (TB_PS.PERCENTAGE_AC / 100)) +
              ((((SUM(stl.REALACTRETAIL_STL) + IF(TB_RETAIL.TOT_QTYTD IS NOT NULL, TB_RETAIL.TOT_QTYTD, 0)) / 21000) * 100) * (TB_RETAIL.PERCENTAGE_AC / 100))
          ) / 3
          ), 2)) ASC) AS NUM_ROW,
	stl.LOCATION_STL AS NAME_LOCATION,
	ROUND((
          (
              ((((SUM(stl.REALACTUB_STL) + IF(TB_UB.TOT_QTYTD IS NOT NULL, TB_UB.TOT_QTYTD, 0)) / 336) * 100) * (TB_UB.PERCENTAGE_AC / 100)) +
              ((((SUM(stl.REALACTPS_STL) + IF(TB_PS.TOT_QTYTD IS NOT NULL, TB_PS.TOT_QTYTD, 0)) / 21000) * 100) * (TB_PS.PERCENTAGE_AC / 100)) +
              ((((SUM(stl.REALACTRETAIL_STL) + IF(TB_RETAIL.TOT_QTYTD IS NOT NULL, TB_RETAIL.TOT_QTYTD, 0)) / 21000) * 100) * (TB_RETAIL.PERCENTAGE_AC / 100))
          ) / 3
          ), 2) AS NEW_AVERAGE
FROM
	(
		SELECT
			*,
			(
				SELECT
					SUM(QTY_TD)
				FROM
					transaction_detail_today tdt
				WHERE
					tdt.TYPE_ACTIVITY LIKE 'Aktivitas UB'
				GROUP BY
					tdt.TYPE_ACTIVITY
	        ) AS TOT_QTYTD
		FROM
			md_activity_category mac
		WHERE
			mac.ID_AC = 1
    ) AS TB_UB,
	(
		SELECT
			*,
			(
				SELECT
					SUM(QTY_TD)
				FROM
					transaction_detail_today tdt
				WHERE
					tdt.TYPE_ACTIVITY LIKE 'Pedagang Sayur'
				GROUP BY
					tdt.TYPE_ACTIVITY
	         ) AS TOT_QTYTD
		FROM
			md_activity_category mac
		WHERE
			mac.ID_AC = 2
  	) AS TB_PS,
	(
		SELECT
			*,
			(
			SELECT
				SUM(QTY_TD)
			FROM
				transaction_detail_today tdt
			WHERE
				tdt.TYPE_ACTIVITY LIKE 'Retail'
			GROUP BY
				tdt.TYPE_ACTIVITY
		   ) AS TOT_QTYTD
		FROM
			md_activity_category mac
		WHERE
			mac.ID_AC = 3
  	) AS TB_RETAIL,
	summary_trans_location stl
WHERE
	stl.updated_at LIKE '2023-01%'
GROUP BY
	stl.LOCATION_STL
ORDER BY
	NUM_ROW ASC