Aggregate information from one table to another with a different “layout” (mysql)-CodePudding

this is my starting table which provides sales information by Id.

Id	Store_Name	Market	Sales	Main_Product
1	StoreA	Rome	10	a
2	StoreB	Rome	15	b
3	StoreC	Rome	9	c
4	Mag1	Paris	10	a
5	Mag2	Paris	23	b
6	Mag3	Paris	12	c
7	Shop1	London	11	a
8	Shop2	London	31	b
9	Shop3	London	45	c
10	Shop4	London	63	d

In order to build a report and create some dynamic sentences, I will need the dataset to be "paginated" as per below table:

Id	Dimension	Dimension_Name	Sales	Main_Product
1	ShoppingCentre	StoreA	10	a
1	Market	Rome	34	a
2	ShoppingCentre	StoreB	15	b
2	Maket	Rome	34	b
3	ShoppingCentre	StoreC	9	c
3	Market	Rome	34	c

Do you have any tip about how to build the last table starting from the first one?

To sum-up:

The new table will be always by Id
Aggregation of market sales happens at row level where every single shopping centre is located

This is the query that I have built so far but wondering if there is a better and more efficient way to accomplish the same:

with store_temp_table as ( 
  select 
  id
  ,Store_Name
  ,Market
, Main_Product
, sum(Sales) as Sales
from Production_Table
where 1=1
group by
1,2,3,4
)
, market_temp_table as (
  select 
  market
, sum(Sales) as Sales
from Production_Table
where 1=1
group by
1
)
,  store_temp_table_refined as(
Select
 a.id
 ,a.Main_Product
 , 'ShoppingCentre' as Dimension_Name
 ,SUM(a.Sales) as Sales
  FROM store_temp_table a INNER JOIN
  market_temp_table b on a.market = b.market

group by
1,2,3
  )
 ,  market_temp_table_refined as (
   Select
 a.id
 ,a.Main_Product
 , 'Market' as DimensionName
  ,SUM(b.Sales) as Sales
    FROM store_temp_table a INNER JOIN
  market_temp_table b on a.market = b.market
group by
1,2,3
     )
   select * from store_temp_table_refined
   union all
   select * from market_temp_table_refined

Thank you

CodePudding user response：

Use a CTE that returns the dimensions that you want and cross join it to a query that returns the columns of the table and an additional column with the total sales of each market:

WITH Dimensions(id, Dimension) AS (VALUES 
  ROW(1, 'ShoppingCentre'), 
  ROW(2, 'Market')
)
SELECT p.Id, 
       d.Dimension,
       CASE d.id WHEN 1 THEN p.Store_Name ELSE p.Market END Dimension_Name, 
       CASE d.id WHEN 1 THEN p.Sales ELSE p.MarketSales END Sales,
       p.Main_Product
FROM Dimensions d 
CROSS JOIN (SELECT *, SUM(Sales) OVER (PARTITION BY Market) AS MarketSales FROM Production_Table) p 
ORDER BY p.id, d.id;

Or, with UNION ALL:

SELECT Id,
       'ShoppingCentre' Dimension,
       Store_Name Dimension_Name, 
       Sales,   
       Main_Product
FROM Production_Table
UNION ALL
SELECT Id,
       'Market',
       Market, 
       SUM(Sales) OVER (PARTITION BY Market),   
       Main_Product
FROM Production_Table
ORDER BY Id, 
         CASE Dimension WHEN 'ShoppingCentre' THEN 1 WHEN 'Market' THEN 2 END;

See the demo.