what is wrong with my query in oracle live sql?

Question

I ran the following code in oracle live SQL but it gives an error. What is the problem?

select * from bricks full join 
((select count(*) from bricks) as "counts"
inner join (select count(*) from bricks group by colour) as "colours" 
on counts.colour=colours.colour)
on bricks.colour=bricks.colour; --a dummy condition just for connecting tables without a specific column

output:

ORA-00907: missing right parenthesis

Answer 1

The problems are:

• AS before a table/sub-query alias is invalid syntax in Oracle. Remove the AS keywords.
• The aliases you are using are quoted identifiers which are case-sensitive and you do not use quoted identifiers with the same case when you refer to the aliases. Remove the double quotes.
• Neither the counts nor colours sub-query has a colour column in the SELECT clause and you cannot subsequently refer to non-existent columns in the ON clause of the join condition.

You can fix it using:

select *
from   bricks
       CROSS JOIN (select count(*) AS cnt from bricks) counts
       LEFT OUTER JOIN (
         select colour, count(*) AS colour_cnt
         from   bricks
         group by colour
       ) colours
       on bricks.colour=colours.colour

Which, for the sample data:

CREATE TABLE bricks (id, colour) AS
SELECT 1, 'red' FROM DUAL UNION ALL
SELECT 2, 'red' FROM DUAL UNION ALL
SELECT 3, 'red' FROM DUAL UNION ALL
SELECT 4, 'green' FROM DUAL UNION ALL
SELECT 5, 'green' FROM DUAL UNION ALL
SELECT 6, 'blue' FROM DUAL;

Outputs:

ID	COLOUR	CNT	COLOUR	COLOUR_CNT
1	red	6	red	3
2	red	6	red	3
3	red	6	red	3
4	green	6	green	2
5	green	6	green	2
6	blue	6	blue	1

---

However, you probably want to simplify things and use the analytic COUNT() OVER (...) function and eliminate the self-joins:

select b.*,
       COUNT(*) OVER () AS cnt,
       COUNT(*) OVER (PARTITION BY colour) AS colour_cnt
from   bricks b;

Which outputs:

ID	COLOUR	CNT	COLOUR_CNT
6	blue	6	1
5	green	6	2
4	green	6	2
2	red	6	3
1	red	6	3
3	red	6	3

(identical other than not duplicating the colours column)

db<>fiddle here