Selecting same column with different where's

[Solo4357]

I've a got query that is using a group by with sums. I need to select a column twice to display different data but a where only would do the same data. In essence:

Col 1 Col 2 Hours Committed hours
100 Item 100 flag A total flag b total

So for item 100, it would sum up hours from a detail table where flag = a AND sum up hours where flag = b. The hours in the detail table is the same column, just selecting different rows to give me a sum in the committed hours than in the Hours column.

I'm really not sure how to go about this. Any ideas?


Here's my initial script:

SELECT JCJOBCAT.JCAT_PHS_CODE, JCJOBHPHS.JHP_NAME, SUM(DISTINCT JCJOBCAT.JCAT_BUDG_AMT) AS EXPR1, SUM(JCDETAIL.JCDT_AMT) AS EXPR2 FROM JCJOBCAT, JCDETAIL, JCJOBHPHS WHERE JCJOBCAT.JCAT_COMP_CODE = JCDETAIL.JCDT_COMP_CODE AND JCJOBCAT.JCAT_JOB_CODE = JCDETAIL.JCDT_JOB_CODE AND JCJOBCAT.JCAT_PHS_CODE = JCDETAIL.JCDT_PHS_CODE AND (JCJOBCAT.JCAT_PHS_CODE = JCJOBHPHS.JHP_CODE AND JCJOBCAT.JCAT_JOB_CODE = JCJOBHPHS.JHP_JOB_CODE) AND ((JCJOBCAT.JCAT_JOB_CODE = :jc_job_code) AND (JCJOBCAT.JCAT_CODE = 'EQRT')) GROUP BY JCJOBCAT.JCAT_PHS_CODE, JCJOBCAT.JCAT_JOB_CODE, JCJOBHPHS.JHP_NAME

What I want is to add in another jcdetail.jcdt_amt on the same line. One would be where jcdetail.job_type = 'O' and the other colum would be where jcdetail.job_type = 'C'

 

[carp]

Try

SELECT JCJOBCAT.JCAT_PHS_CODE, 
       JCJOBHPHS.JHP_NAME, 
       jcdetail.job_type,
       SUM(DISTINCT JCJOBCAT.JCAT_BUDG_AMT) AS EXPR1, 
       SUM(JCDETAIL.JCDT_AMT) AS EXPR2 
FROM  JCJOBCAT, 
      JCDETAIL, 
      JCJOBHPHS 
WHERE JCJOBCAT.JCAT_COMP_CODE  = JCDETAIL.JCDT_COMP_CODE   
  AND JCJOBCAT.JCAT_JOB_CODE  = JCDETAIL.JCDT_JOB_CODE  
  AND JCJOBCAT.JCAT_PHS_CODE  = JCDETAIL.JCDT_PHS_CODE 
  AND (JCJOBCAT.JCAT_PHS_CODE  = JCJOBHPHS.JHP_CODE  
       AND JCJOBCAT.JCAT_JOB_CODE  = JCJOBHPHS.JHP_JOB_CODE
       )  
  AND ((JCJOBCAT.JCAT_JOB_CODE  = :jc_job_code) 
        AND (JCJOBCAT.JCAT_CODE = 'EQRT')
      ) 
  and jcdetail.job_type in ('O','C')
GROUP BY JCJOBCAT.JCAT_PHS_CODE, 
         JCJOBHPHS.JHP_NAME,
         jcdetail.job_type;

 

[Solo4357]

That helps but gives me 1 row with O and 1 row with C. What I want is the 'O' sum and the 'C' sum in the same row but different columns.

 

[Solo4357]

Would a Case statement work for this?

 

[carp]

OK - I might know what you're asking for and how to get it.

SELECT JCJOBCAT.JCAT_PHS_CODE, 
       JCJOBHPHS.JHP_NAME, 
       sum(decode(jcdetail.job_type,'O',jcdetail.jcdt_amt,0) o_sum,
       sum(decode(jcdetail.job_type,'C',jcdetail.jcdt_amt,0) c_sum,
       SUM(DISTINCT JCJOBCAT.JCAT_BUDG_AMT) AS EXPR1, 
       SUM(JCDETAIL.JCDT_AMT) AS EXPR2 
FROM  JCJOBCAT, 
      JCDETAIL, 
      JCJOBHPHS 
WHERE JCJOBCAT.JCAT_COMP_CODE  = JCDETAIL.JCDT_COMP_CODE   
  AND JCJOBCAT.JCAT_JOB_CODE  = JCDETAIL.JCDT_JOB_CODE  
  AND JCJOBCAT.JCAT_PHS_CODE  = JCDETAIL.JCDT_PHS_CODE 
  AND (JCJOBCAT.JCAT_PHS_CODE  = JCJOBHPHS.JHP_CODE  
       AND JCJOBCAT.JCAT_JOB_CODE  = JCJOBHPHS.JHP_JOB_CODE
       )  
  AND ((JCJOBCAT.JCAT_JOB_CODE  = :jc_job_code) 
        AND (JCJOBCAT.JCAT_CODE = 'EQRT')
      ) 
GROUP BY JCJOBCAT.JCAT_PHS_CODE, 
         JCJOBHPHS.JHP_NAME,
         jcdetail.job_type;

 

[Solo4357]

Thanks for your help on this btw.

That's close but I'm still getting two rows. I run it and get:


JCAT_PHS_CODE JHP_NAME O_SUM C_SUM EXPR1
---------------------------------------------------------
0100 Branch Con 0 58185.01 83541
0100 Branch Con 24938.47 0 83541
0200 fdk 0 123 345
0200 fdk 678 0 345
What I would like to get is:

JCAT_PHS_CODE JHP_NAME O_SUM C_SUM EXPR1
---------------------------------------------------------
0100 Branch Con 24938.47 58185.01 83541
0200 fdk 678 123 345

 

[SatanClaus]

Hi Solo,

if i did get you right, this could be what you're looking for:

SELECT JCJOBCAT.JCAT_PHS_CODE,
       JCJOBHPHS.JHP_NAME,
       sum(jc_o.jcdt_amt) o_sum,
       sum(jc_c.jcdt_amt) c_sum,
       SUM(DISTINCT JCJOBCAT.JCAT_BUDG_AMT) AS EXPR1,
       SUM(JCDETAIL.JCDT_AMT) AS EXPR2
FROM  JCJOBCAT,
      (SELECT * FROM JCDETAIL WHERE job_type = 'O') jc_o,
      (SELECT * FROM JCDETAIL WHERE job_type = 'C') jc_c,
      JCJOBHPHS
WHERE JCJOBCAT.JCAT_COMP_CODE  = jc_o.JCDT_COMP_CODE   
  AND JCJOBCAT.JCAT_COMP_CODE  = jc_c.JCDT_COMP_CODE
  AND JCJOBCAT.JCAT_JOB_CODE  = jc_o.JCDT_JOB_CODE  
  AND JCJOBCAT.JCAT_JOB_CODE  = jc_c.JCDT_JOB_CODE  
  AND JCJOBCAT.JCAT_PHS_CODE  = jc_o.JCDT_PHS_CODE
  AND JCJOBCAT.JCAT_PHS_CODE  = jc_c.JCDT_PHS_CODE  
  AND (JCJOBCAT.JCAT_PHS_CODE  = JCJOBHPHS.JHP_CODE  
       AND JCJOBCAT.JCAT_JOB_CODE  = JCJOBHPHS.JHP_JOB_CODE
       )  
  AND ((JCJOBCAT.JCAT_JOB_CODE  = :jc_job_code)
        AND (JCJOBCAT.JCAT_CODE = 'EQRT')
      )
GROUP BY JCJOBCAT.JCAT_PHS_CODE,
         JCJOBHPHS.JHP_NAME,
         jcdetail.job_type;

I hope this helps.

Greetings from Germany
;-)
SatanClaus

 

[carp]

My goof! I forgot to fix the GROUP BY clause:

[code]
SELECT JCJOBCAT.JCAT_PHS_CODE, 
       JCJOBHPHS.JHP_NAME, 
       sum(decode(jcdetail.job_type,'O',jcdetail.jcdt_amt,0) o_sum,
       sum(decode(jcdetail.job_type,'C',jcdetail.jcdt_amt,0) c_sum,
       SUM(DISTINCT JCJOBCAT.JCAT_BUDG_AMT) AS EXPR1, 
       SUM(JCDETAIL.JCDT_AMT) AS EXPR2 
FROM  JCJOBCAT, 
      JCDETAIL, 
      JCJOBHPHS 
WHERE JCJOBCAT.JCAT_COMP_CODE  = JCDETAIL.JCDT_COMP_CODE   
  AND JCJOBCAT.JCAT_JOB_CODE  = JCDETAIL.JCDT_JOB_CODE  
  AND JCJOBCAT.JCAT_PHS_CODE  = JCDETAIL.JCDT_PHS_CODE 
  AND (JCJOBCAT.JCAT_PHS_CODE  = JCJOBHPHS.JHP_CODE  
       AND JCJOBCAT.JCAT_JOB_CODE  = JCJOBHPHS.JHP_JOB_CODE
       )  
  AND ((JCJOBCAT.JCAT_JOB_CODE  = :jc_job_code) 
        AND (JCJOBCAT.JCAT_CODE = 'EQRT')
      ) 
GROUP BY JCJOBCAT.JCAT_PHS_CODE, 
         JCJOBHPHS.JHP_NAME;

 

[Solo4357]

Sweet! That worked great, carp! Thanks a bunch!