Question 252929 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Cross table sum join in postgres

I have this table:

product amount no_of_customers
Prdct_1 100 9
Prdct_1 200 15
Prdct_2 50 2
Prdct_3 40 5
Prdct_3 80 5
Prdct_4 10 1

I want to join this with the below table

product no_of_bills
Prdct_1 6
Prdct_1 3
Prdct_2 4
Prdct_3 10
Prdct_3 5
Prdct_4 2

Additionally, I want to add a sum of all categories. So the result should look like this:

product amount no_of_customers no_of_bills
Prdct_1 300 24 9
Prdct_2 50 2 4
Prdct_3 120 10 15
Prdct_4 10 1 2
Total 480 35 30

How could I solve it? Thanks for your answers.

Tags:

3

Answers


  1. 0

    I do not have postgres to test immediately, but look at this sql, Hope this works for you…

    SELECT
  product,
  SUM(amount) AS amount,
  SUM(no_of_customers) AS no_of_customers,
  SUM(no_of_bills) AS no_of_bills
FROM (
  SELECT
    product,
    amount,
    no_of_customers
  FROM table1
  UNION ALL
  SELECT
    product,
    0 AS amount,
    0 AS no_of_customers,
    no_of_bills
  FROM table2
) AS t
GROUP BY product
ORDER BY product;

    The sql creates a temporary table t from the two tables using UNION ALL operation which allows merging tables irrespective of duplicated rows exists or not.
    The query then groups the data in the table t by the product column wherein all rows with the same product name are grouped together.
    For each group, the query sums up the values in columns amount, no_of_customers, and no_of_bills columns, and returns the results in the following order: product, amount, no_of_customers, no_of_bills.

    That’s the logic

    Login or Signup to reply.
  2. 0

    I’m not sure if this is the simplest possible solution, but it works for me. I named your tables products and bills:

    WITH product_summary AS (
  SELECT
    p.product,
    SUM(p.amount) AS amount,
    SUM(p.no_of_customers) AS no_of_customers
  FROM
    products p
  GROUP BY
    p.product
),
bill_summary AS (
  SELECT
    b.product,
    SUM(b.no_of_bills) AS no_of_bills
  FROM
    bills b
  GROUP BY
    b.product
)
SELECT *
FROM (
  SELECT
    ps.product,
    COALESCE(ps.amount, 0) AS amount,
    COALESCE(ps.no_of_customers, 0) AS no_of_customers,
    COALESCE(bs.no_of_bills, 0) AS no_of_bills
  FROM
    product_summary ps
  FULL JOIN
    bill_summary bs
  ON
    ps.product = bs.product
  UNION ALL
  SELECT
    'Total' AS product,
    SUM(COALESCE(ps.amount, 0)) AS total_amount,
    SUM(COALESCE(ps.no_of_customers, 0)) AS total_customers,
    SUM(COALESCE(bs.no_of_bills, 0)) AS total_bills
  FROM
    product_summary ps
  FULL JOIN
    bill_summary bs
  ON
    ps.product = bs.product
) AS result
ORDER BY
  result.product;

    I’m using the Common Table Expression (CTE) product_summary to calculate the sum of amount and no_of_customers for each product in the products table. The CTE bill_summary calculates the sum of no_of_bills for each product in the bills table. These two results are then combined with UNION ALL.

    Login or Signup to reply.
  3. 0

    You can use group by with grouping sets to calculate subtotals and a total, then use join to combine both results from CTEs:

    with product_customers_cte as (
  select COALESCE (product,'Total') as product, sum(amount) as amount, sum(no_of_customers) as no_of_customers
  from product_customers
  group by
    grouping sets (
        (product),
        ()
    )
),
product_bills_cte as (
  select COALESCE (product,'Total') as product, sum(no_of_bills) as no_of_bills
  from product_bills
  group by
    grouping sets (
        (product),
        ()
    )
)
select pc.*, pb.no_of_bills
from product_customers_cte pc
join product_bills_cte pb on pb.product = pc.product
order by product

    Result :

    product amount  no_of_customers no_of_bills
Prdct_1 300     24              9
Prdct_2 50      2               4
Prdct_3 120     10              15
Prdct_4 10      1               2
Total   480     37              30

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search