daily pastebin goal
77%
SHARE
TWEET

Untitled

a guest Jun 14th, 2018 52 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     customer.id,
  3.     customer.name,
  4.     count(DISTINCT p_orders.id) AS num_orders,
  5.     sum(p_items.price) AS total_money
  6. FROM
  7.     customer
  8.     INNER JOIN p_orders ON p_orders.id_customer = customer.id
  9.     INNER JOIN p_items ON p_items.id_order = p_orders.id
  10. GROUP BY
  11.     customer.id,
  12.     customer.name,
  13.     p_orders.id_customer
  14. ORDER BY
  15.     customer.id
  16.    
  17. (
  18.     SELECT
  19.         customer.id,
  20.         customer.name,
  21.         count(DISTINCT p_orders.id) AS num_orders,
  22.         sum(p_items.price) AS total_money
  23.     FROM
  24.         customer
  25.         INNER JOIN p_orders ON p_orders.id_customer = customer.id
  26.         INNER JOIN p_items ON p_items.id_order = p_orders.id
  27.     GROUP BY
  28.         customer.id,
  29.         customer.name,
  30.         p_orders.id_customer
  31. )
  32. UNION
  33. (
  34.     SELECT
  35.         customer.id,
  36.         customer.name,
  37.         count(DISTINCT h_orders.id) AS num_orders,
  38.         sum(h_items.price) AS total_money
  39.     FROM
  40.         customer
  41.         INNER JOIN h_orders ON h_orders.id_customer = customer.id
  42.         INNER JOIN h_items ON h_items.id_order = h_orders.id
  43.     GROUP BY
  44.         customer.id,
  45.         customer.name,
  46.         h_orders.id_customer
  47. )
  48. ORDER BY id ASC
  49.    
  50. (
  51.     --SELECT 2
  52. )
  53. UNION
  54. (
  55.     --SELECT 1
  56. )
  57. GROUP BY id
  58. ORDER BY id ASC
  59.    
  60. CREATE TABLE customer (
  61.     id serial NOT NULL,
  62.     name character(50)
  63. );
  64. CREATE TABLE p_orders (
  65.     id serial NOT NULL,
  66.     id_customer integer NOT NULL,
  67.     date date DEFAULT now(),
  68.     code character(5)
  69. );
  70. CREATE TABLE p_items (
  71.     id serial NOT NULL,
  72.     id_order integer NOT NULL,
  73.     descr character(250),
  74.     price money
  75. );
  76. CREATE TABLE h_orders (
  77.     id integer NOT NULL,
  78.     id_customer integer NOT NULL,
  79.     date date,
  80.     code character(5)
  81. );
  82. CREATE TABLE h_items (
  83.     id integer NOT NULL,
  84.     id_order integer NOT NULL,
  85.     descr character(250),
  86.     price money
  87. );
  88. CREATE UNIQUE INDEX id_h_orders ON h_orders USING btree (id);
  89. CREATE INDEX id_h_o_c ON h_orders USING btree (id_customer);
  90. CREATE UNIQUE INDEX id_items_h ON h_items USING btree (id);
  91. CREATE INDEX id_ordinr_dsve ON h_items USING btree (id_order);
  92.  
  93. ALTER TABLE ONLY customer
  94.     ADD CONSTRAINT customer_pkey  (id);
  95. ALTER TABLE ONLY p_orders
  96.     ADD CONSTRAINT p_orders_pkey PRIMARY KEY (id);
  97. ALTER TABLE ONLY p_items
  98.     ADD CONSTRAINT p_items_pkey PRIMARY KEY (id);
  99. ALTER TABLE ONLY stats
  100.     ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
  101. ALTER TABLE ONLY p_orders
  102.     ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
  103. ALTER TABLE ONLY p_items
  104.     ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES p_orders(id) ON DELETE CASCADE;
  105. ALTER TABLE ONLY h_orders
  106.     ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
  107. ALTER TABLE ONLY h_items
  108.     ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES h_orders(id) ON DELETE CASCADE;
  109.    
  110. CREATE VIEW All_Orders
  111. AS
  112.      SELECT
  113.           id,
  114.           id_customer,
  115.           code,
  116.           date,
  117.           'H' AS order_type
  118.      FROM
  119.           h_orders
  120.      UNION ALL
  121.      SELECT
  122.           id,
  123.           id_customer,
  124.           code,
  125.           date,
  126.           'P' AS order_type
  127.      FROM
  128.           p_orders
  129.  
  130. CREATE VIEW All_Order_Items  -- A table name of "items" is pretty bad in my opinion
  131. AS
  132.      SELECT
  133.           id,
  134.           id_order,
  135.           description,
  136.           price,
  137.           'H' AS order_item_type
  138.      FROM
  139.           h_items
  140.      UNION ALL
  141.      SELECT
  142.           id,
  143.           id_order,
  144.           description,
  145.           price,
  146.           'P' AS order_item_type
  147.      FROM
  148.           p_items
  149.    
  150. SELECT tbl.ID,
  151.        tbl.Name,
  152.        sum(tbl.num_orders) num_orders,
  153.        sum(tbl.total_money) total_money
  154. FROM (    
  155.       SELECT customer.id,
  156.              customer.name,        
  157.              count(DISTINCT p_orders.id) AS num_orders,        
  158.              sum(p_items.price) AS total_money    
  159.       FROM customer        
  160.             INNER JOIN p_orders
  161.                 ON p_orders.id_customer = customer.id        
  162.             INNER JOIN p_items
  163.                 ON p_items.id_order = p_orders.id    
  164.       GROUP BY customer.id, customer.name, p_orders.id_customer
  165.  
  166.       UNION
  167.  
  168.       SELECT customer.id,
  169.              customer.name,        
  170.              count(DISTINCT h_orders.id) AS num_orders,
  171.              sum(h_items.price) AS total_money    
  172.       FROM  customer        
  173.              INNER JOIN h_orders
  174.                  ON h_orders.id_customer = customer.id
  175.              INNER JOIN h_items
  176.                  ON h_items.id_order = h_orders.id    
  177.       GROUP BY customer.id, customer.name, h_orders.id_customer
  178.     ) tbl
  179.  GROUB BY tbl.id, tbl.name
  180.  ORDER BY tbl.id ASC
  181.    
  182. Create view customerOrders
  183. AS
  184. SELECT      customer.id as CustomerID,  customer.name, p_orders.id as OrderID,  p_items.price  as price
  185. FROM        customer        
  186. INNER JOIN  p_orders ON p_orders.id_customer = customer.id        
  187. INNER JOIN  p_items ON p_items.id_order = p_orders.id
  188. union all
  189. SELECT      customer.id,  customer.name,  h_orders.id as id, H_items.price          
  190. FROM        customer        
  191. INNER JOIN  h_orders ON h_orders.id_customer = customer.id        
  192. INNER JOIN  h_items ON h_items.id_order = h_orders.id
  193.    
  194. SELECT    CustomerID,    customer.name,    count(DISTINCT OrderID) AS num_orders,    
  195. sum(price) AS total_money
  196. FROM    customerOrders
  197. GROUP BY     CustomerID,    customer.name
  198. ORDER BY    CustomerID
  199.    
  200. SELECT * FROM p_orders
  201. UNION
  202. SELECT * FROM h_orders
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top