Complex Oracle SQL to sum data

Question

I am building a SQL query in Oracle 10g that needs to sum overtime hours for the base table, but needs fields from the parent tables as well. I am looking for the fastest way to build the query. The Project_ID in the first three tables is the same, where the Work_ID is one-to-many to the fourth table.

Table J
Project_ID VARCHAR(6)
J_Title VARCHAR(25)

Table K
Project_ID VARCHAR(6)
K_Title VARCHAR(25)

Table L
Project_ID VARCHAR(6)
L_Title VARCHAR(25)
Work_ID VARCHAR(6)

Table C
Work_ID VARCHAR(6)
L_Title VARCHAR(25)
CHRG_DT DATE
WRK_HR NUMBER(5,2)

My best guess looks like this (It's simplified, and the date is actually passed as a variable to the stored procedure):

SELECT
 J.J_Title,
 K.K_Title,
 L.L_Title,
 L.Work_ID,
SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
FROM C,
L,
K,
J
WHERE J.Project_ID = 'ABC'
AND K.Project_ID = J.Project_ID
AND L.Project_ID = K.Project_ID
AND C.Work_ID = L.Work_ID
GROUP BY L.Work_ID,
L.Project_ID,
K.Project_ID,
J.J_Title,
K.K_Title,
L.L_Title

UPDATE: Omitted two fields in Table C. Changed Group By fields, and added Work_ID to output for clarity. Sorry for changes, I have to type this from another network, can't just cut and paste. Also trying to leave out unrelated fields.

This is the output I am getting:

J_Title K_Title L_Title Work_ID WRK_HR
------- ------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC  DEF         30 
JOB_ABC KO_ABC  LO_ABC  DFG          3

This is the output I am looking for:

J_Title K_Title L_Title WRK_HR
------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC      33

Answer 1

I think you might be able to get the results you want with:

select j_title, k_title, l_title, sum(wrk_hr) from
(
  SELECT
   J.J_Title,
   K.K_Title,
   L.L_Title,
   L.Work_ID,
  SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
  FROM C, L, K, J
  WHERE J.Project_ID = 'ABC'
  AND K.Project_ID = J.Project_ID
  AND L.Project_ID = K.Project_ID
  AND C.Work_ID = L.Work_ID
  GROUP BY L.Work_ID, L.Project_ID, K.Project_ID, J.J_Title, K.K_Title, L.L_Title
)
group by j_title, k_title, l_title;