Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Have you actually tried that? Does it work? –  FrustratedWithFormsDesigner Nov 25 '13 at 15:05
    
Yes, but it groups by Work_ID, and I need it to group by Project_ID. I need help with the structure and optimizing; I am thinking a subquery that SELECTS from table C for each row of Table L will be really slow. –  CigarDoug Nov 25 '13 at 15:28
    
Can you post the complete table definitions. Your query is referencing columns that are not part of your definitions. –  Sebastian Meine Nov 25 '13 at 15:33
    
My mistake. Added omitted fields. –  CigarDoug Nov 25 '13 at 15:56
    
@CigarDoug: Would you be able to add a simple sample of the current output, and a simple sample of the desired output? –  FrustratedWithFormsDesigner Nov 25 '13 at 15:58

1 Answer 1

up vote 1 down vote accepted

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;
share|improve this answer
    
That works, thanks. I understand you have to include fields in the Group By statement to pass them to the parent query, but does the order of the Group By matter? –  CigarDoug Nov 26 '13 at 12:33
    
@CigarDoug: The order of columns in GROUP BY normally does not matter (try it out with some test data and see!), but if you start including grouping functions (oracle-base.com/articles/misc/…) such as ROLLUP or CUBE then it can matter. –  FrustratedWithFormsDesigner Nov 26 '13 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.