Select * from subquery

60

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.

This question is tagged with sql oracle

~ Asked on 2012-01-18 14:19:43

The Best Answer is


142

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

~ Answered on 2012-01-18 14:21:02


Most Viewed Questions: