clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756594 fiddles created (13555 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;
category_id name products all_products
2 Phones 2 6
3 Tablets 2 6
1 Laptops 2 6
 hidden batch(es)