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

PostgreSQL multiplies columns when joining two tables

I have two tables similar to this example (simplified for clarity):

parent:

| id   |  name     | amount    | year  |
----------------------------------------
| 101  |  Henry    | 300       | 2020  |
----------------------------------------
| 102  |  Carol    | 100       | 2020  |
----------------------------------------
| 103  |  Tom      | 900       | 2020  |
----------------------------------------

child:

| id   |  parent_id  | department    |
--------------------------------------
| 1    |  101        | finance       |
--------------------------------------
| 2    |  101        | hr            |
--------------------------------------
| 3    |  101        | it            |
--------------------------------------
| 4    |  102        | support       |
--------------------------------------

I am trying to join two tables and get sum value of the amount field by the following script, but the sum would be 1900 instead of 1300 as the record 101 is repeated 3 times:

select 
sum(p.amount),
count(c) 
from parent p
left join child c on c.parent_id = p.id 
where p.year = 2020

When I use the following query, it works, but it gives count of child as 2 instead of 4 as it grouped the child records based on their parent:

SELECT SUM(p.amount) 
FROM parent p
LEFT JOIN (
    SELECT parent_id 
    FROM child
    GROUP BY parent_id
) c ON c.parent_id = p.id
WHERE p.year = 2020;

So, how to fix this problem?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I used the following approach and fixed the problem by providing correct sum of the amounts in the parent table and count for the child one:

    SELECT
    (SELECT SUM(amount) FROM parent WHERE year = 2020),
    COUNT(c) 
FROM child c;

    If there is any suggestion to improve this answer, feel free to share your suggestions.


  2. 0

    This query joins the tables, calculates the count of child records for each parent, and then sums up the parent amount and child count.

    SELECT 
    SUM(p.amount) AS total_amount,
    sum(child_count) AS child_count
FROM parent p
LEFT JOIN (
    SELECT parent_id, COUNT(*) AS child_count
    FROM child
    GROUP BY parent_id
) c ON c.parent_id = p.id
WHERE p.year = 2020

    While your query uses a subquery in the SELECT clause, which might be less efficient. Additionally, it doesn’t explicitly join the tables, which might lead to confusion about how the child count is related to the parent records.

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

Back To Top
Search