TechGuy TechGuy - 6 days ago 7
SQL Question

Sum and Join Two tables in Oracle

I have two tables: Base table and Detail table. I need to get the count from the Detail table with Base table.

Tables structure



Base table

Nid UserId BaseVal
--------------------
1 150 100


Detail table

Did Nid SeqVal
----------------
1 1 500
2 1 600


I want this:

Result Table

BaseVal SeqVal
---------------
100 1100


I have tried this query but I can get only the Summation value. I need BaseVal too.

SELECT SUM(SeqVal) as SEQVAL
FROM TBL_DETAIL
WHERE Nid =: Nid

Answer

There are multiple answers possible. Assuming your base table always has 1 record and detail table has multiple rows per base table record, a possible answer can be

SELECT 
    AVG(BaseVal) Sum_BaseVal, SUM(SeqVal) Sum_SeqVal 
From BaseTable T1, DetailTable T2 
WHERE T1.Nid = T2.Nid 
      AND T1.Nid = :1

Bind T1.Nid with right value to ensure you get values for required Nid.

Note: You can also use above query with a GROUP BY Nid (it may be necessary if your problem statement is simplified version of a complex query)

Other possible answers can be (I'm giving only 1 to save time, but more exists)

WITH 
    SeqSum as (SELECT SUM(SeqVal) Sum_SeqVal FROM DetailTable WHERE Nid = 1)
SELECT BaseVal, Sum_SeqVal 
FROM BaseTable , SeqSum 
WHERE Nid = 1

You can further tune queries to have proper conditions if any..

Comments