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


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;


COLOUR
COUNTS


red
3

red
3

red
3

green
2

green
2

blue
1


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


COLOUR
COUNTS
TOTAL


blue
1
6

green
2
6

red
3
6


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


COLOUR
COUNTS
TOTAL


red
3
6

green
2
6

blue
1
6

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