By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601434 fiddles created (47986 in the last week).
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)
);
✓
✓
✓
hidden batch(es)
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);
6 rows affected
3 rows affected
6 rows affected
hidden batch(es)
SELECT * from categories;
category_id
name
1
Laptops
2
Phones
3
Tablets
…
hidden batch(es)
SELECT * FROM products;
product_id
name
1
Macbook Pro
2
Macbook Air
3
iPhone 11
4
Google Pixel 4
5
iPad Mini 4
6
iPad
…
hidden batch(es)
SELECT categories_products.category_id,
COUNT(*) OVER() AS all_products
FROM categories_products;
category_id
all_products
1
6
1
6
2
6
2
6
3
6
3
6
…
hidden batch(es)
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
category_id
name
products
all_products
1
Laptops
2
6
2
Phones
2
6
3
Tablets
2
6
…
hidden batch(es)
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;
