0

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?

2 Answers 2

0

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.

0

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".

1
  • 1 to 31 Days are columns with Every day sales number.
    – nityasetty
    May 22, 2019 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.