I'd like to get sum of column1, sum of column2 and total sum. In Postgres I can do it this way: (notice the star)
SELECT *, a+b AS total_sum FROM
(
SELECT SUM(column1) AS a, SUM(column2) AS b
FROM table
)
But in Oracle I get an syntax error and have to use this:
SELECT a,b, a+b AS total_sum FROM
(
SELECT SUM(column1) AS a, SUM(column2) AS b
FROM table
)
I have really lot of columns to return so I do not want to write the column names again in the main query. Is there any easy solution?
I cannot use a+b in the inner query because there are not known at this place.
I do not want to use SELECT SELECT SUM(column1) AS a, SUM(column2) AS b, SUM(column1)+SUM(column2) AS total_sum
.
You can select every column from that sub-query by aliasing it and adding the alias before the *
:
SELECT t.*, a+b AS total_sum
FROM
(
SELECT SUM(column1) AS a, SUM(column2) AS b
FROM table
) t
Source: Stackoverflow.com