Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;
6 rows affected

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


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


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


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

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