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