Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- customer.id,
- customer.name,
- count(DISTINCT p_orders.id) AS num_orders,
- sum(p_items.price) AS total_money
- FROM
- customer
- INNER JOIN p_orders ON p_orders.id_customer = customer.id
- INNER JOIN p_items ON p_items.id_order = p_orders.id
- GROUP BY
- customer.id,
- customer.name,
- p_orders.id_customer
- ORDER BY
- customer.id
- (
- SELECT
- customer.id,
- customer.name,
- count(DISTINCT p_orders.id) AS num_orders,
- sum(p_items.price) AS total_money
- FROM
- customer
- INNER JOIN p_orders ON p_orders.id_customer = customer.id
- INNER JOIN p_items ON p_items.id_order = p_orders.id
- GROUP BY
- customer.id,
- customer.name,
- p_orders.id_customer
- )
- UNION
- (
- SELECT
- customer.id,
- customer.name,
- count(DISTINCT h_orders.id) AS num_orders,
- sum(h_items.price) AS total_money
- FROM
- customer
- INNER JOIN h_orders ON h_orders.id_customer = customer.id
- INNER JOIN h_items ON h_items.id_order = h_orders.id
- GROUP BY
- customer.id,
- customer.name,
- h_orders.id_customer
- )
- ORDER BY id ASC
- (
- --SELECT 2
- )
- UNION
- (
- --SELECT 1
- )
- GROUP BY id
- ORDER BY id ASC
- CREATE TABLE customer (
- id serial NOT NULL,
- name character(50)
- );
- CREATE TABLE p_orders (
- id serial NOT NULL,
- id_customer integer NOT NULL,
- date date DEFAULT now(),
- code character(5)
- );
- CREATE TABLE p_items (
- id serial NOT NULL,
- id_order integer NOT NULL,
- descr character(250),
- price money
- );
- CREATE TABLE h_orders (
- id integer NOT NULL,
- id_customer integer NOT NULL,
- date date,
- code character(5)
- );
- CREATE TABLE h_items (
- id integer NOT NULL,
- id_order integer NOT NULL,
- descr character(250),
- price money
- );
- CREATE UNIQUE INDEX id_h_orders ON h_orders USING btree (id);
- CREATE INDEX id_h_o_c ON h_orders USING btree (id_customer);
- CREATE UNIQUE INDEX id_items_h ON h_items USING btree (id);
- CREATE INDEX id_ordinr_dsve ON h_items USING btree (id_order);
- ALTER TABLE ONLY customer
- ADD CONSTRAINT customer_pkey (id);
- ALTER TABLE ONLY p_orders
- ADD CONSTRAINT p_orders_pkey PRIMARY KEY (id);
- ALTER TABLE ONLY p_items
- ADD CONSTRAINT p_items_pkey PRIMARY KEY (id);
- ALTER TABLE ONLY stats
- ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
- ALTER TABLE ONLY p_orders
- ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
- ALTER TABLE ONLY p_items
- ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES p_orders(id) ON DELETE CASCADE;
- ALTER TABLE ONLY h_orders
- ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
- ALTER TABLE ONLY h_items
- ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES h_orders(id) ON DELETE CASCADE;
- CREATE VIEW All_Orders
- AS
- SELECT
- id,
- id_customer,
- code,
- date,
- 'H' AS order_type
- FROM
- h_orders
- UNION ALL
- SELECT
- id,
- id_customer,
- code,
- date,
- 'P' AS order_type
- FROM
- p_orders
- CREATE VIEW All_Order_Items -- A table name of "items" is pretty bad in my opinion
- AS
- SELECT
- id,
- id_order,
- description,
- price,
- 'H' AS order_item_type
- FROM
- h_items
- UNION ALL
- SELECT
- id,
- id_order,
- description,
- price,
- 'P' AS order_item_type
- FROM
- p_items
- SELECT tbl.ID,
- tbl.Name,
- sum(tbl.num_orders) num_orders,
- sum(tbl.total_money) total_money
- FROM (
- SELECT customer.id,
- customer.name,
- count(DISTINCT p_orders.id) AS num_orders,
- sum(p_items.price) AS total_money
- FROM customer
- INNER JOIN p_orders
- ON p_orders.id_customer = customer.id
- INNER JOIN p_items
- ON p_items.id_order = p_orders.id
- GROUP BY customer.id, customer.name, p_orders.id_customer
- UNION
- SELECT customer.id,
- customer.name,
- count(DISTINCT h_orders.id) AS num_orders,
- sum(h_items.price) AS total_money
- FROM customer
- INNER JOIN h_orders
- ON h_orders.id_customer = customer.id
- INNER JOIN h_items
- ON h_items.id_order = h_orders.id
- GROUP BY customer.id, customer.name, h_orders.id_customer
- ) tbl
- GROUB BY tbl.id, tbl.name
- ORDER BY tbl.id ASC
- Create view customerOrders
- AS
- SELECT customer.id as CustomerID, customer.name, p_orders.id as OrderID, p_items.price as price
- FROM customer
- INNER JOIN p_orders ON p_orders.id_customer = customer.id
- INNER JOIN p_items ON p_items.id_order = p_orders.id
- union all
- SELECT customer.id, customer.name, h_orders.id as id, H_items.price
- FROM customer
- INNER JOIN h_orders ON h_orders.id_customer = customer.id
- INNER JOIN h_items ON h_items.id_order = h_orders.id
- SELECT CustomerID, customer.name, count(DISTINCT OrderID) AS num_orders,
- sum(price) AS total_money
- FROM customerOrders
- GROUP BY CustomerID, customer.name
- ORDER BY CustomerID
- SELECT * FROM p_orders
- UNION
- SELECT * FROM h_orders
Add Comment
Please, Sign In to add comment