CODEWITHSUNDEEP
sqlsql-serversumwindow-functions
Reema B
Reema B

Reputation: 1

sum() query in sql

I want to show up the result of the equation in sql :

A : 
0.5
0.25
1
1
1

I need to take a current row and divide it by the sum of rows, For ex: 0.5/3.75

I tried this but shows me an error :

select A / sum(A) 
from table X

Upvotes: 0

Views: 68

Answers (2)

BayouKid
BayouKid

Reputation: 79

The way you've used SUM is as an aggregate function. You can't blend row-level and aggregate values in one line.

In most databases, SUM can also be turned into a windowing function with the OVER clause, which I think will do what you want. Try this:

select A / sum(A) OVER () AS ratioValue
from tableX

Upvotes: 3

GMB
GMB

Reputation: 222462

Just window that SUM():

select a / sum(a) over() as ratio from mytable

Expression sum(a) over() computes the sum of a over the whole table, and makes it available in each and every row.

On the other hand, sum(a) (without the order by) is an aggregate function, that can only be used in the context of group by (either explicit or implicit). Typically, this query gives you the total sum:

select sum(a) as sum_a from mytable

If we were to use this and not use window functions, we would re-phrase your original query like so:

select a / (select sum(a) from mytable) as ratio from mytable

But of course it is much simpler and more efficient to use the window sum here.

Upvotes: 1

Related Questions