why the COUNTS_BY_COLOUR.COUNTS is invalid identifier in oracle apex

The following code gives an error when I specify the name of the columns(select counts_by_colour.colour,counts_by_colour.counts ), but if I say select * the code is executed. Why?

select counts_by_colour.colour,counts_by_colour.counts
from (select *
        from bricks
        CROSS JOIN (
        select count(*) as "total" from bricks) counts
        INNER JOIN (
         select colour, count(*) as "counts"
         from   bricks
         group by colour
       ) colours
       on bricks.colour=colours.colour) "counts_by_colour";

output:

ORA-00904: "COUNTS_BY_COLOUR"."COUNTS": invalid identifier

>Solution :

You have used a quoted identifier "counts_by_colour" and quoted identifiers a case sensitive.

In contrast, unquoted identifiers are implicitly converted to upper-case so counts_by_colour is converted to COUNTS_BY_COLOUR.

When you compare the two, "counts_by_colour" and COUNTS_BY_COLOUR are not the same identifiers as they have different cases so COUNTS_BY_COLOUR is not recognized.

You will find the same issue later as you have the quoted "counts" and the unquoted counts.

The solution is (for the most part) to NEVER use quoted identifiers.

More fundamentally, using the colours_by_counts alias not necessary (and may be syntactically invalid), as is using so many nested brackets, as you can refer to the table and sub-query aliases:

select colours.colour,
       colours.counts
from   bricks
       CROSS JOIN (select count(*) as total from bricks) counts
       INNER JOIN (
         select colour, count(*) as counts
         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:

COLOUR	COUNTS
red	3
red	3
red	3
green	2
green	2
blue	1

If you do not want the duplicates then just use the aggregated sub-queries:

select *
from   (
         select colour, count(*) as counts
         from   bricks
         group by colour
       ) colours
       CROSS JOIN (select count(*) as total from bricks) counts;

Or, more simply, with analytic functions:

select colour,
       COUNT(*) AS counts,
       SUM(COUNT(*)) OVER () AS total
from   bricks
GROUP BY colour;

Which both output:

COLOUR	COUNTS	TOTAL
red	3	6
green	2	6
blue	1	6

db<>fiddle here