<!-- --> > CREATE TABLE products(product_id SERIAL PRIMARY KEY, name TEXT); > CREATE TABLE categories(category_id SERIAL PRIMARY KEY, name TEXT); > CREATE TABLE categories_products( > category_id INTEGER REFERENCES categories(category_id), > product_id INTEGER REFERENCES products(product_id) > ); > > <pre> > ✓ > > ✓ > > ✓ > </pre> <!-- --> > INSERT INTO products(name) VALUES > ('Macbook Pro'), > ('Macbook Air'), > ('iPhone 11'), > ('Google Pixel 4'), > ('iPad Mini 4'), > ('iPad'); > INSERT INTO categories(name) VALUES > ('Laptops'), > ('Phones'), > ('Tablets'); > INSERT INTO categories_products VALUES > (1, 1), > (1, 2), > (2, 3), > (2, 4), > (3, 5), > (3, 6); > > <pre> 6 rows affected > 3 rows affected > 6 rows affected > </pre> <!-- --> > SELECT * from categories; > > <pre> > category_id | name > ----------: | :------ > 1 | Laptops > 2 | Phones > 3 | Tablets > </pre> <!-- --> > SELECT * FROM products; > > <pre> > product_id | name > ---------: | :------------- > 1 | Macbook Pro > 2 | Macbook Air > 3 | iPhone 11 > 4 | Google Pixel 4 > 5 | iPad Mini 4 > 6 | iPad > </pre> <!-- --> > SELECT categories_products.category_id, > COUNT(*) OVER() AS all_products > FROM categories_products; > > <pre> > category_id | all_products > ----------: | -----------: > 1 | 6 > 1 | 6 > 2 | 6 > 2 | 6 > 3 | 6 > 3 | 6 > </pre> <!-- --> > SELECT DISTINCT > categories.category_id, > categories.name, > COUNT(categories_products.product_id) OVER(PARTITION BY categories_products.category_id) AS products, > COUNT(categories_products.product_id) OVER() AS all_products > FROM categories > INNER JOIN categories_products > ON categories.category_id = categories_products.category_id > > <pre> > category_id | name | products | all_products > ----------: | :------ | -------: | -----------: > 1 | Laptops | 2 | 6 > 2 | Phones | 2 | 6 > 3 | Tablets | 2 | 6 > </pre> <!-- --> > SELECT > categories.category_id, > categories.name, > COUNT(*) AS products, > SUM(COUNT(*)) OVER() AS all_products > FROM categories_products > INNER JOIN categories > ON categories.category_id = categories_products.category_id > GROUP BY categories.category_id, categories.name; > > <pre> > category_id | name | products | all_products > ----------: | :------ | -------: | -----------: > 2 | Phones | 2 | 6 > 3 | Tablets | 2 | 6 > 1 | Laptops | 2 | 6 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=0982f87a71197d69c689d6d9d8dae085)*