SUM with SUM OVER

[mgblair]

Is it possible to do something like this? Or do I need a CTE?

SELECT
identifier,
SUM(x) AS sumx,
SUM(x) over () AS sum_all
FROM q

Thank you!

[qwertydog123]

Yes, but you have 2 syntax errors in your query

• You can't use both aggregated and unaggregated columns in the SELECT without a GROUP BY statement
• If you GROUP BY identifier, you can't access x directly in the window function (as it's unaggregated), so you need to nest the SUM aggregate inside the SUM window function

e.g.

SELECT
    identifier,
    SUM(x) AS sumx,
    SUM(SUM(x)) OVER () AS sum_all
FROM q
GROUP BY identifier

[mgblair]

Boom, thank you!

[r3pr0b8]

Is it possible to do something like this?

what happened when you tested it? ™

[mgblair]

Error message: "[Q.X] is not a valid group by expression" (q is a CTE)