how do I get extra fields with in a column with calculations

Question

I have a table AR--X with columns as Region, Product type, pofit, Target, 1, 2, 3,....... till 31 ( days ) as columns.

In Product type Column, there are fields like Electronic, Plastic, wood, other, xx.

Now I want:

Select region, product type, profit, 1,2... 31 from AR--X.

In the results I would like to see:

for Product type with extra fields with calculations

1. a Field with a name as 'extra' = Target - (Electronic+ Plastic+ wood +other+ xx )
2. a Field with a name as 'Total'= Sum(Electronic+ Plastic+ wood +other + xx )
3. a Field with a name as '% of total' = total/Target*100.

Is this possible?

Answer 1

Generated columns(MySQL/MariaDB) make these fields possible.

Depending on what you are doing the 1-31 columns for days may not make sense. It will be hard to do partial month retrievals. Your queries appear to be month totals so it might be ok.

Answer 2

Assuming you are asking to display extra columns, then:

SELECT  region,
        product_type,
        profit,
        Target - (Electronic+ Plastic+ wood +other+ xx ) AS 'extra',
        (Electronic+ Plastic+ wood +other+ xx ) AS 'Total',
        (Electronic+ Plastic+ wood +other+ xx ) / Target*100 AS '% of total'
    FROM tbl

You will find that having 31 columns for 31 days to be a mistake. It will lead to some messy queries. Instead, have rows in another table. And if you need to have output with a column for each day, see "pivoting".