How do I return the sum for this query?

I have the following tables I need to find开发者_开发百科 out the sum.

Table A

ID Name
1 Jason
2 Peter 
3 Ravi

Table B

ID ID_SEC
1 11
1 12
1 13
2 21
2 22
2 23
3 31
3 32
3 33

Table C

ID_SEC  Value   Include_Ind
11  100         Y
12  200         Y
13  300         N
21  10          Y
22  20          N 
23  30          N
31  1000        N
32  2000        N
33  3000        N

Output

ID Name  Total  Include_Ind_count [only count when Y]
1 Jason  600     2
2 Peter  60      1
3 Ravi  6000     0

Use:

  SELECT a.id,
         a.name,
         SUM(c.value) AS total
    FROM TABLE_A a
    JOIN TABLE_B b ON b.id = a.id
    JOIN TABLE_C c ON c.id_sec = b.id_sec
GROUP BY a.id, a.name

The trick to counting INCLUDE_IND only when the flag is set to 'Y' is to use CASE() to test its value:

SQL> select a.id
  2          , a.name
  3         , sum ( c.val) as total
  4         , count( case when c.include_ind = 'Y' then 1
  5                       else null end ) as inc_ind_cnt
  6  from a
  7          join b on ( b.id = a.id )
  8          join c on ( c.id_sec = b.id_sec )
  9  group by a.name, a.id
 10  order by a.id
 11  /

        ID NAME            TOTAL INC_IND_CNT
---------- ---------- ---------- -----------
         1 Jason             600           2
         2 Peter              60           1
         3 Ravi             6000           0

SQL>

The ORDER BY is necessary to guarantee sort order since Oracle changed the algorithm it uses for GROUP BY operations in 10g.

You can use inner Joins and SUM for getting the result - Assuming you tableC.Value is int field. Else you need to cast it.

SELECT tabA.id, tabA.name, SUM(tabC.value)
FROM TABLE_A tabA
INNER JOIN TABLE_B tabB ON tabB.id = tabA.id
INNER JOIN TABLE_C tabc ON tabC.id_sec = tabB.id_sec
GROUP BY tabA.id, tabA.name