Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Sum Rows based on a condition

    I'm using a db2 database (not sure of the version). My script is functional but I'm not producing the results I expect. I need to separate my aggregate totals in to 3 groups based on conditions in my case statement but for every project under the same project_name/id and building_name/id, I need the sum total under each bucket having only one row per project_name/id and building_name/id. I assume a group by of some sort or a recursive function would be the solution but I'm not quite sure. Would appreciate a push in the right direction. Here is my script. The attached thumbnails display the current results and desired output.

    Code:
    SELECT
            DP.DIM_PROJECT_ID,
            DP.PROJECT_NAME,
            DM.DIM_BUILDING_ID,
            DM.BUILDING_NAME,
            CASE WHEN ( DJ.GROUPS3 IN ('33.3% <= x <= 100%', '16.7% <= x < 33.3%') AND DA.TYPE_NAME = 'SALES')  
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 IN ('60% <= x <= 100%', '20% <= x < 60%') AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            ELSE '0'    
            END as CAPABILITY,      
    
            CASE WHEN (DJ.GROUPS3 = '0% <= x < 16.7%' AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 = '0% <= x < 20%' AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            ELSE '0'            
            END as GROUP_1, 
    
            CASE WHEN (DJ.GROUPS3 = '16.7% <= x < 33.3%' AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 = '20% <= x < 60%' AND DA.TYPE_NAME = 'SALES') 
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer)     
            ELSE '0'        
            END as GROUP_2,
    
            CASE WHEN (DJ.GROUPS3 = '33.3% <= x <= 100%' AND DA.TYPE_NAME = 'SALES') 
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 = '60% <= x <= 100%' AND DA.TYPE_NAME = 'SALES') 
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer)
            ELSE '0'            
            END as GROUP_3
    
    FROM FACT_TABLE as FAT
    RIGHT JOIN DIM_ALLOCATION DA ON FAT.DIM_ALLOCATION_ID = DA.DIM_ALLOCATION_ID
    INNER JOIN DIM_PROJECT DP ON FAT.DIM_PROJECT_ID = DP.DIM_PROJECT_ID
    INNER JOIN DIM_DATE DD ON FAT.ALLOCATION_START_DATE_DIM_ID = DD.DATE_KEY
    INNER JOIN DIM_JOB DJ ON FAT.DIM_JOB_ID = DJ.DIM_JOB_ID
    INNER JOIN DIM_BUILDING DM ON FAT.DIM_BUILDING_ID = DM.DIM_BUILDING_ID
    
    WHERE
        DD.DATE_VALUE = '2013'
        AND DA.MACHINE_NAME IN ('ADMIN', 'INVISION')
    
    
    GROUP BY DM.DIM_BUILDING_ID,
             DP.DIM_PROJECT_ID, 
             DP.PROJECT_NAME,
             CAPABILITY,
             DM.BUILDING_NAME, 
             DJ.GROUPS3,
             DA.TYPE_NAME
    ORDER BY DP.PROJECT_NAME
    Attached Thumbnails Attached Thumbnails Capture1.PNG   Capture.PNG  
    Last edited by wtolbert; 09-24-13 at 17:41.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can do stuff like this:

    Code:
    Select name,
    sum(case when category = 1 then 1 else 0 end) as total_cat_1,
    sum(case when category = 2 then 1 else 0 end) as total_cat_2,
    sum(case when category = 1 then 1 else 0 end) as total_cat_3
    from my_table
    group by name
    Andy

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    I tried the following but receive an error -112 "The operand of an aggregate function includes an aggregate function, an OLAP specification, or a scalar fullselect"


    Code:
    SUM(CASE WHEN ( DJ.GROUPS3 IN ('33.3% <= x <= 100%', '16.7% <= x < 33.3%') AND DA.TYPE_NAME = 'SALES')  
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 IN ('60% <= x <= 100%', '20% <= x < 60%') AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            ELSE '0'    
            END) as CAPABILITY,   
       
            SUM(CASE WHEN (DJ.GROUPS3 = '0% <= x < 16.7%' AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 = '0% <= x < 20%' AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            ELSE '0'            
            END) as GROUP_1, 
    
            SUM(CASE WHEN (DJ.GROUPS3 = '16.7% <= x < 33.3%' AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 = '20% <= x < 60%' AND DA.TYPE_NAME = 'SALES') 
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer)     
            ELSE '0'        
            END) as GROUP_2,
    
            SUM(CASE WHEN (DJ.GROUPS3 = '33.3% <= x <= 100%' AND DA.TYPE_NAME = 'SALES') 
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 = '60% <= x <= 100%' AND DA.TYPE_NAME = 'SALES') 
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer)
            ELSE '0'            
            END) as GROUP_3

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are trying to SUM a SUM.

    Andy

  5. #5
    Join Date
    Aug 2013
    Posts
    14
    What I'm trying to do is sum up capability, group_1, group_2, and group_3 where project_name and building_name are the same having a single row for each building_name and project. The initial aggregation provides the sum total based on the condition but in some cases will have multiple rows which i need to combine to a single total. An example of the desired results I'm looking for is attached.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Before getting into your exact question. Do you realize that the RIGHT join you have on RIGHT JOIN DIM_ALLOCATION DA is being changed back to an INNER join after you have performed the RIGHT JOIN? For LEFT and RIGHT joins you never want predicates for those tables in the WHERE clause, as you then defeat the purpose of the right/left join, as your resultset must have those conditions or you get nothing back.

    Couple of things.

    1. Let's thin out these case statements. In each WHEN condition you have
    Code:
    AND DA.TYPE_NAME = 'SALES'
    . So, let's take it out of the WHEN and put it down into the JOIN on the DIM_ALLOCATION table. Added benefit, it will speed you up a bit as other data will not be returned for you to just put a 0 that you don't need.

    2. Get in the habit of using ELSE NULL instead of ELSE 0. 0's are added, nulls are ignored.

    3. Lets move that SUM to the outside of your CASE statements.
    So:
    Code:
    CASE WHEN ( DJ.GROUPS3 IN ('33.3% <= x <= 100%', '16.7% <= x < 33.3%') AND DA.TYPE_NAME = 'SALES')  
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            WHEN (DJ.GROUPS3 IN ('60% <= x <= 100%', '20% <= x < 60%') AND DA.TYPE_NAME = 'SALES')
            THEN cast(sum(cast(FAT.TRANSACTION_AMOUNT as real)) as integer) 
            ELSE '0'    
            END as CAPABILITY,
    Should become:
    Code:
    cast(SUM(CASE WHEN DJ.GROUPS3 IN ('33.3% <= x <= 100%', '16.7% <= x < 33.3%')
            THEN cast(FAT.TRANSACTION_AMOUNT as real)
            WHEN DJ.GROUPS3 IN ('60% <= x <= 100%', '20% <= x < 60%')
            THEN cast(FAT.TRANSACTION_AMOUNT as real)
            ELSE NULL
            END)) as integer)  as CAPABILITY,
    4. The grouping should be on:
    Code:
    GROUP BY DM.DIM_BUILDING_ID,
             DP.DIM_PROJECT_ID, 
             DP.PROJECT_NAME,
             DM.BUILDING_NAME
    Keep in mind, none of this tested, just off the cuff.

    Dave

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This isn't what you want, but it might be a stepping stone. Give it a shot and see if it produces something useful.
    Code:
    SELECT
       DP.DIM_PROJECT_ID
    ,  DP.PROJECT_NAME
    ,  DM.DIM_BUILDING_ID
    ,  DM.BUILDING_NAME
    ,  DJ.GROUPS3
    ,  DA.TYPE
    ,  Sum(FAT.TRANSACTION_AMOUNT) as GRAND_TOTAL,      
    ,  Sum(CASE WHEN DA.TYPE_NAME <> 'SALES'
          THEN FAT.TRANSACTION_AMOUNT END) AS NON_SALES
    ,  Sum(CASE WHEN DA.TYPE_NAME  = 'SALES'
          THEN FAT.TRANSACTION_AMOUNT END) AS SALES
       FROM FACT_TABLE as FAT
       INNER JOIN DIM_PROJECT DP ON FAT.DIM_PROJECT_ID = DP.DIM_PROJECT_ID
       INNER JOIN DIM_DATE DD ON FAT.ALLOCATION_START_DATE_DIM_ID = DD.DATE_KEY
       INNER JOIN DIM_JOB DJ ON FAT.DIM_JOB_ID = DJ.DIM_JOB_ID
       INNER JOIN DIM_BUILDING DM ON FAT.DIM_BUILDING_ID = DM.DIM_BUILDING_ID
       RIGHT JOIN DIM_ALLOCATION DA ON FAT.DIM_ALLOCATION_ID = DA.DIM_ALLOCATION_ID
       WHERE  DD.DATE_VALUE = '2013'
          AND DA.MACHINE_NAME IN ('ADMIN', 'INVISION')
       GROUP BY DP.DIM_PROJECT_ID,  DP.PROJECT_NAME,  DM.DIM_BUILDING_ID
    ,  DM.BUILDING_NAME,  DJ.GROUPS3,  DA.TYPE
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Aug 2013
    Posts
    14
    Thanks Dave! The code below with the group by worked perfectly.

    Code:
    cast(SUM(CASE WHEN DJ.GROUPS3 IN ('33.3% <= x <= 100%', '16.7% <= x < 33.3%')
            THEN cast(FAT.TRANSACTION_AMOUNT as real)
            WHEN DJ.GROUPS3 IN ('60% <= x <= 100%', '20% <= x < 60%')
            THEN cast(FAT.TRANSACTION_AMOUNT as real)
            ELSE NULL
            END)) as integer)  as CAPABILITY,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •