how to create a third column by manipulating two columns in SQL?-CodePudding

I have two columns that look like this:

Name	Apple	Orange.
A	2.	1
A	3.	1
A	. 1.	1
B	. 2	4.
B	. 3.	2

Now I wanted to take the sum of two columns for each row and divide each value by the sum and convert it into a percentage and display it in the third. Now expected result is

Name	Apple	Orange.	. Result
A	2.	1	66.67% Apple, 33.33% Orange.
A	3.	1	75.00% Apple, 25.00% Orange.
A	. 1.	1	50.00% Apple, 50.00% Orange.
B	. 2	4.	33.33% Apple, 66.67% Orange.
B	. 3.	2	60.00% Apple, 40.00% Orange.

Here is the SQL code used in postgres

CREATE TABLE Persons (
    Name varchar(255),
    Apple int,
    Orange int
);

INSERT INTO Persons (Name, Apple, Orange)
VALUES ('A', '2', '1'), 
        ('A', '3', '1'),
        ('A', '1', '1'),
        ('B', '2', '4'),
        ('B', '3', '2'
       );

select *
from Persons;

CodePudding user response：

You can try in this way. The answer below is for mysql or sql.

SELECT name, apple, orange,
CONCAT(ROUND(CAST((apple * 100.0 / (apple orange)) AS FLOAT), 2), '% Apple, ',
       ROUND(CAST((orange * 100.0 / (apple orange)) AS FLOAT), 2), '% Orange')
AS result
FROM Persons;

In order to get the percentage, you need to get the sum first, thus, I use 'apple orange'

Check out this db fiddle