how to reference an expression from the previous list value in sql?-CodePudding

So I'm not sure if I explained my question correctly in the title but I'll try saying it here. Let's say you have a table like this

How can you, in a single query, add up the grades and create a new column showcasing each row's percent of total grades like this:

I've tried doing something like

Select SUM( Grade) As total, Grade/total as Percent total

but it doesn't seem to work this way. I know I can obviously do two queries one for the sum and then make it happen directly. Can anyone tell help me do the percent total in a single query?

CodePudding user response：

You can use a window function. For example:

select *, 100 * grade / sum(grade) over() as percent_total from t

CodePudding user response：

You could cross-join this query with a query that returns a single row of the sum:

SELECT     t.*, 100 * t.grade / s.sum_grades AS "Percent Total"
FROM       mytable t
CROSS JOIN (SELECT SUM(grade) AS sum_grades
            FROM   mytable) s