All geek questions in one place

SQL: grouping 2 tables as 1 with join, join, and then?

I have 5 tables:

clients id - name

p_orders id - id_customer - code - date

p_items id - id_order - description - price

and h_orders and h_items, which are exactly a copy of p_orders and p_items.

When p_ tables reach a large number of rows, I move the oldest to h_ tables .. they are due to history.

So my problem is: how to get data from p_ tables and h_, treating them as one unique table ?

For example, I want to get the number of orders for each customer and the total price (of all customer orders ), and I use this query:

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

it only works for one “set” of tables (p_ or h_) .. but I want both of them.

I tried using UNION:

(
    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

, p_, h_, 2 num_orders total_money (, p_ tables h_ tables)

GROUP BY id :

(
    --SELECT 2
)
UNION
(
    --SELECT 1
)
GROUP BY id
ORDER BY id ASC

ERROR: "GROUP" 948, , GROUP BY .

?

EDIT:

uriDium, , id , (aka p_orders.id_customer) . db- ( , , - ):

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;
+3
6

, :

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

. (P H), , "id". ( "h" "p" , Orders All_Order_Items. , . , id , .

+4

:

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
+2

, . USe Union , , , . , .

, .

- ( :

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
+1

, SQL Server . UNION ALL . , SQL Server , . 1 p 1 h?

0

, , - (, "a_orders" "a_items" ). :

SELECT * FROM p_orders
UNION
SELECT * FROM h_orders

a_orders, h_orders ( ), UNION ALL UNION.

0

, ..

" ", " " . , , ... ( ?)

, .

Anyway, if I can, can I ask you if the structure and indexes I used are good or can be optimized?

0
source

Source: https://habr.com/ru/post/1708895/

More articles:


All Articles