sqloracle-databaseselectaliasunion-all

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


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.


Solution

  • 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>