Help with Simple Query?

Question

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

Any help ?

Answer 1

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

Answer 2

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

Answer 3

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.