KyusuMart Planogram Analysis

planogram-analysis-mysql-subquery-with-alias-table-name.sql

#author          : Agung Pambudi
#email           : mail@agungpambudi.com
#version         : 0.1
#
#KyusuMart Planogram Analysis
#==============================================================================
#                                   _         _ _
# ___ ___ _ _ ___ ___ ___ ___ _____| |_ _ _ _| |_|  ___ ___ _____
#| .'| . | | |   | . | . | .'|     | . | | | . | |_|  _| . |     |
#|__,|_  |___|_|_|_  |  _|__,|_|_|_|___|___|___|_|_|___|___|_|_|_|
#    |___|       |___|_|


SELECT FIRST_ALIAS.effectiveDate, activeStore, indStore, allItem, qtyINDStore, qtyINDStore/allItem * 100 AS percent, qtyINDStore/activeStore AS averageStore, indStore/activeStore * 100 AS indAudit, stockOut FROM 

(SELECT effectiveDate, SUM(allItem) AS allItem FROM (SELECT '15/12/2021' AS effectiveDate, store, COUNT(product_code) AS allItem FROM largeplanogram_211215 WHERE planogramStatus IN ('B','I') GROUP BY store) alias_table GROUP BY effectiveDate) FIRST_ALIAS, 

(SELECT effectiveDate, SUM(ind) AS qtyINDStore FROM (SELECT '15/12/2021' AS effectiveDate, store, COUNT(product_code) AS indItem FROM largeplanogram_211215 WHERE planogramStatus='I' AND productType='TT' AND stock <> 0 GROUP BY store) alias_table GROUP BY effectiveDate) SECOND_ALIAS, 

(SELECT effectiveDate, COUNT(store) AS indStore FROM (SELECT '15/12/2021' AS effectiveDate, store FROM largeplanogram_211215 WHERE planogramStatus='B' GROUP BY store) alias_table GROUP BY effectiveDate) THIRD_ALIAS, 

(SELECT effectiveDate, COUNT(store_code) AS activeStore FROM (SELECT '15/12/2021' AS effectiveDate, store_code, `cost` FROM sliplocationproduct_211215 WHERE `code` = 14 AND `cost` > 5 GROUP BY store_code) alias_table GROUP BY effectiveDate) FOURTH_ALIAS, 

(SELECT effectiveDate, AVG(`cost`) AS stockOut FROM (SELECT '15/12/2021' AS effectiveDate, store_code, `cost` FROM sliplocationproduct_211215 WHERE `code` = 17 GROUP BY store_code) alias_table GROUP BY effectiveDate) FIFTH_ALIAS 

WHERE FIRST_ALIAS.effectiveDate=SECOND_ALIAS.effectiveDate AND FIRST_ALIAS.effectiveDate=THIRD_ALIAS.effectiveDate AND FIRST_ALIAS.effectiveDate=FOURTH_ALIAS.effectiveDate AND FIRST_ALIAS.effectiveDate=FIFTH_ALIAS.effectiveDate;