SQL query in Oracle to sum up values from an alias column

Question

In Oracle I would like to make a SELECT statement combined with UNION ALL to count all rows in some tables (second column) and assign a description to each row (first column). Below is what I actually have and the result.

SELECT 'Amsterdam' AS city, count(*) AS amount FROM A
UNION ALL
SELECT 'Berlin' AS city, count(*) AS amount FROM B
UNION ALL
SELECT ALL 'California' AS city, count(*) AS amount FROM C;

CITY	AMOUNT
Amsterdam	3
Berlin	2
California	4

I would also like to add to the result last row with TOTAL as CITY and 9 (sum of all values in AMOUNT column) as AMOUNT. Below is the visualization.

CITY	AMOUNT
Amsterdam	3
Berlin	2
California	4
TOTAL	9

I struggle with it because I can't find a way to make another operation (SUM in this case) on a column that doesn't exist (CITY), because it is produced by my query. This is what I tried to do, but I get ORA-00942: table or view does not exist

SELECT city, amount 
FROM
    (SELECT 'Amsterdam' AS city, count(*) AS amount FROM A
     UNION ALL
     SELECT 'Berlin' AS city, count(*) AS amount FROM B
     UNION ALL
     SELECT ALL 'California' AS city, count(*) AS amount FROM C) t
UNION ALL
SELECT 'TOTAL' AS city, SUM(t.amount) AS amount FROM t;

I will be thankful for any help.

Answer 1

How about rollup?

SQL> with temp as
  2  -- this is your current query
  3  (
  4  SELECT 'Amsterdam' AS city, count(*) AS amount FROM A
  5  UNION ALL
  6  SELECT 'Berlin' AS city, count(*) AS amount FROM B
  7  UNION ALL
  8  SELECT ALL 'California' AS city, count(*) AS amount FROM C
  9  )
 10  -- your current query ends here
 11  select
 12    case when grouping_id(city) = 1 then 'Total' else city end as city,
 13    sum(amount) amount
 14  from temp
 15  group by rollup(city);

CITY           AMOUNT
---------- ----------
Amsterdam           3
Berlin              2
California          4
Total               9

SQL>

---

Or, the way you tried:

SQL> with temp as
  2  -- this is your current query
  3  (
  4  SELECT 'Amsterdam' AS city, count(*) AS amount FROM A
  5  UNION ALL
  6  SELECT 'Berlin' AS city, count(*) AS amount FROM B
  7  UNION ALL
  8  SELECT ALL 'California' AS city, count(*) AS amount FROM C
  9  )
 10  -- your current query ends here
 11  select * from temp
 12  union all
 13  select 'Total', sum(amount)
 14  from temp;

CITY           AMOUNT
---------- ----------
Amsterdam           3
Berlin              2
California          4
Total               9

SQL>

Answer 2

As I can see that you have unique CITY names, You can use GROUP BY ROLLUP as follows:

SELECT COALESCE(CITY,'TOTAL'), SUM(AMOUNT) FROM
( -- YOUR QUERY
SELECT 'Amsterdam' AS city, count(*) AS amount FROM A
UNION ALL
SELECT 'Berlin' AS city, count(*) AS amount FROM B
UNION ALL
SELECT ALL 'California' AS city, count(*) AS amount FROM C
)
GROUP BY ROLLUP (CITY);

Answer 3

Another option is to read number of rows using Oracle's table USER_TABLES/ALL_TABLES:

Select Case TABLE_NAME
            When 'A_TBL' Then 'Amsterdam'
            When 'B_TBL' Then 'Berlin'
            When 'C_TBL' Then 'California'
      End "CITY",
      NUM_ROWS "AMOUNT"
From user_tables 
Where TABLE_NAME IN('A_TBL', 'B_TBL', 'C_TBL')
UNION ALL
Select 'TOTAL', Sum(NUM_ROWS)
From user_tables 
Where TABLE_NAME IN('A_TBL', 'B_TBL', 'C_TBL');

/*  R e s u l t :
CITY         AMOUNT
-----------  ------
Amsterdam         3
Berlin            2
California        4
TOTAL             9    */

... take care of OWNER too if needed ... see the fiddle here