How would I use divide each row by the SUM of another column?

Question

The database is as follows:

ID	Classification	emissions	market_value
1	Type A	0.04	5.67
2	Type B	0.01	6.12

I am trying to add a column whose formula will be as follows:

(emissions * market_value) / SUM(market_value) AS Contribution

Doing so will turn the database into this:

ID	Classification	emissions	market_value	contribution
1	Type A	0.04	5.67	0.192
2	Type B	0.01	6.12	0.005

I've tried writing my scripts as follow but have had no luck:

• Attempt 1:

SELECT
id,
classification,
emissions,
market_value
(emissions * market_value / (SELECT SUM(market_value) from database)) AS contribution
FROM database 

However, the platform I am doing SQL on, Denodo, does not allow for subqueries within a select statemen.

I've also tried rewriting my query as follows:

• Attempt 2:

SELECT
id,
classification,
emissions,
market_value,
(emissions* market_value / SUM(market_value)) AS contribution
FROM database 
GROUP BY id, classification, emissions, market_value

But this doesn't seem to work either. The SUM function using this format just returns the same value as the "emissions" column per row for some reason.

Answer 1

I have no denodo to try it out, but it seems to support Common Table Expression (CTE). https://community.denodo.com/docs/html/browse/6.0/vdp/vql/queries_select_statement/with_clause/with_clause

Try this maybe:

WITH sum_table (mval_sum) AS
    (SELECT SUM(market_value) AS mval_sum
       FROM database)
SELECT id
     , classification
     , emissions
     , market_value
     , (emissions * market_value / mval_sum) AS contribution
  FROM database
  JOIN sum_table

MySQL 8.0 Playground: https://www.db-fiddle.com/f/ajfdrdXsgK1z4UnrDgbPCL/0