I have around ten tables with the same dimension of columns and with the same columnnames. These table are united with UNION ALL
.
Now I want to create a customized column and add the name of the tables to all of these different tables.
SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
ROUND(SUM(quantity), 2) AS "Quantity",
ROUND(SUM(value) , 2) AS "ContractValue",
product AS "Product",
intention AS "Intention"
FROM table1
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, kelag_intention
UNION ALL
SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
ROUND(SUM(quantity), 2) AS "Quantity",
ROUND(SUM(value) , 2) AS "ContractValue",
product AS "Product",
intention AS "Intention"
FROM table2
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, kelag_intention
I want a new column named as "Branch", where I can create for each table a specific entry, like for table1 the row entries of Branch
should be should be Optimization Sales" and for table2 it should be "Validation Sales".
How can I do this?
You can add a static value for the "Branch" column in each of your SELECT statements within the UNION ALL.
SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
ROUND(SUM(quantity), 2) AS "Quantity",
ROUND(SUM(value) , 2) AS "ContractValue",
product AS "Product",
intention AS "Intention",
'Optimization Sales' AS "Branch" -- Branch specific to table1
FROM table1
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, intention
UNION ALL
SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
ROUND(SUM(quantity), 2) AS "Quantity",
ROUND(SUM(value) , 2) AS "ContractValue",
product AS "Product",
intention AS "Intention",
'Validation Sales' AS "Branch" -- Branch specific to table2
FROM table2
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, intention;