CODEWITHSUNDEEP
sqloracle-database
Petr Shypila
Petr Shypila

Reputation: 1499

Calculate data in a second column using data from the first one

I need to create a SQL query which calculates some data. For instance, I have such SQL query:

SELECT SUM(AMOUNT) FROM FIRMS WHERE FIRM_ID IN(....) GROUP BY FIRM;

which produces such data:

28,740,573
30,849,923
25,665,724
43,223,313
34,334,534
35,102,286
38,556,820
19,384,871

Now, in a second column I need to show relation between one entry and sum of all entries. Like that:

28,740,573 | 0.1123
30,849,923 | 0.1206
25,665,724 | 0.1003
43,223,313 | 0.1689
34,334,534 | 0.1342
35,102,286 | 0.1372
38,556,820 | 0.1507
19,384,871 | 0.0758

For instance, sum of all entries from first column above is gonna be 255,858,044 and the value in a first entry, second cell is gonna be 28,740,573 / 255,858,044 = 0.1123. And same for each entry in a result.

How can I do that?

UPD: Thanks @a_horse_with_no_name, I forgot to DBMS. It's Oracle.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Most databases now support the ANSI standard window functions. So, you can do:

SELECT SUM(AMOUNT),
       SUM(AMOUNT) / SUM(SUM(AMOUNT)) OVER () as ratio
FROM FIRMS
WHERE FIRM_ID IN (....)
GROUP BY FIRM;

Note: Some databases do integer division. So, if AMOUNT is an integer, then you need to convert to a non-integer number in these databases. One easy method is to multiple by 1.0.

Upvotes: 6

Related Questions