TechGuy TechGuy - 8 months ago 59
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.

WHERE Nid =: Nid

Answer Source

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

    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)

    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..