title: SQL: grouping 2 tables as 1 with join, union, and then?
body: <p>I have 5 tables:</p>
  
  <p>customers
  id - name</p>
  
  <p>p_orders
  id - id_customer - code - date</p>
  
  <p>p_items
  id - id_order - description - price</p>
  
  <p>and h_orders and h_items, that are exactly the copy of p_orders and p_items.</p>
  
  <p>When the p_ tables reach a big amount of rows, i move the oldest to the h_ tables.. they due as history.</p>
  
  <p>So, my problem is: <strong>how to retrieve the data from both the p_ tables and h_ considering them as one unique table</strong>?</p>
  
  <p>For example, i want to retrieve the number of orders for each customer, and the total price (<strong>of all the customer's orders</strong>), and i use that query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>it works just for one 'set' of tables (p_ or h_)..but i want them both.</p>
  
  <p>I've tryed to use an UNION:</p>
  
  <pre><code>(
      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
  </code></pre>
  
  <p>This one works, but if a customer have orders both in the p_ tables and in the h_ tables, i'll have 2 rows for that customer with 2 different num_orders and total_money (respectively coming from p_ tables and h_ tables)</p>
  
  <p>I've tryed to add a GROUP BY id outside the union:</p>
  
  <pre><code>(
      --SELECT 2
  )
  UNION
  (
      --SELECT 1
  )
  GROUP BY id
  ORDER BY id ASC
  </code></pre>
  
  <p>but the query fail with <em>ERROR:  syntax error at or near "GROUP" at character 948</em>, seem like GROUP BY cannot be used in that way.</p>
  
  <p>Any suggestion?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>For uriDium, yes, all the tables have the id column as primary key, and the referred fields (aka p_orders.id_customer) are foreign keys too.
  Here the test db structure dump (i added some indexes and foreign keys after the table creation, but i dont think that this mean something):</p>
  
  <pre><code>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;
  </code></pre>