Skip to content
Advertisement

SQL Inner join with sum and null value

The table below is an extract of a larger set of data enter image description here

In my scenario Column 2 is null when is the “parent” record (Column 1 = AB1 and Column 2 is NULL) and as you can see the following 2 “child” records under Column 2 have AB1 as identifier which matches the AB1 from Column 1, what I want to do is to sum the values on Column 3 when Column 2 has the same identifier (AB1), up to this point the sum = 29 (for this case I can do a SUM and group by AB1). My issue arises when I need to add the value of 10 in Column 3 when column 2 is NULL and Column 1 is AB1 (parent identifier). The common identifier is AB1 but for the parent record the identifier is in Column 1 instead of Column 2. I need a SQL that return a total sum of 39.

Edit:

Thanks for the prompt responses, my apologies I think my question was not clear enough. I am using MS SQL Server Management Studio The goal for the query to sum the amounts on Column 3 by grouping by the records on Column 2 that have the same identifier (AB1) and then find that same identifier on Column 1 (AB1) and also add that value to the total sum. The query below is doing the group by Column 2 correctly because for example if I have 10 records with the identifier AB1 it is returning one row with the sum of the amounts on Column 3, the issue is that I also need to add to that sum when the identifier AB1 is also in Column 1.

select t1.Column1 , round(sum (t1.Column3),2) as Total from table t1, table t2 where
and t1.Column2 = t2. Column1 group by t1. Column2

Basically this table stores transactions and the initial transaction “parent” is in Column 1 (AB1) and all other transactions “children” linked to the parent transaction have that identifier (AB1) but in Column 2. Column 1 is a unique identifier and does not repeat and then is the “parent” transaction it is NULL on Column 2 but that identifier (AB1) can be repeated multiple times in Column 2 depending all the “children” transactions that are linked to the “parent”.

Advertisement

Answer

Oracle
The WITH clause is here just to generate sample data and, as such, it is not the part of the answer.
I don’t know what is the expected result, but the Totals could be calculated using Union All (without Inner Join)

WITH
    tbl AS
        (
            Select 'AB1' "COL_1", Null  "COL_2", 10 "COL_3" From Dual Union All
            Select 'CD2' "COL_1", 'AB1' "COL_2", 15 "COL_3" From Dual Union All
            Select 'EF3' "COL_1", 'AB1' "COL_2", 14 "COL_3" From Dual 
        )
SELECT
    ID, Sum(TOTAL) "TOTAL"
FROM
    (
        SELECT COL_1 "ID", Sum(COL_3)  "TOTAL" FROM tbl GROUP BY COL_1 UNION ALL
        SELECT COL_2 "ID", Sum(COL_3)  "TOTAL" FROM tbl GROUP BY COL_2
    )
WHERE ID Is Not Null
GROUP BY ID
ORDER BY ID
--
--  R e s u l t
--  
ID       TOTAL
--- ----------
AB1         39 
CD2         15 
EF3         14

It is a Sum() Group By aggregation, but the same result gives Sum() analytic function with DISTINCT keyword.

SELECT DISTINCT
    ID, Sum(TOTAL) OVER(PARTITION BY ID ORDER BY ID) "TOTAL"
FROM
    (
        SELECT COL_1 "ID", Sum(COL_3)  "TOTAL" FROM tbl GROUP BY COL_1 UNION ALL
        SELECT COL_2 "ID", Sum(COL_3)  "TOTAL" FROM tbl GROUP BY COL_2
    )
WHERE ID Is Not Null
--
--  R e s u l t
--  
ID       TOTAL
--- ----------
AB1         39 
CD2         15 
EF3         14

And if you need Inner Join then the answer is below. Note that there is only ID which actually has children. That is because of the Inner Join. Regards…

SELECT 
    t1.COL_1 "ID", 
    Max(t1.COL_3) + Sum(t2.COL_3) "TOTAL"
FROM
    tbl t1
INNER JOIN
    tbl t2 ON (t2.COL_2 = t1.COL_1)
GROUP BY t1.COL_1
ORDER BY t1.COL_1
--
--  R e s u l t
--  
ID       TOTAL
--- ----------
AB1         39 
Advertisement