none
Sum two fields on different rows in same table to display as third row in result

    Question

  • Hi All,

    I'm running version SQL Server 2008R2. I'm trying to add together two items from the same field but from different rows based on the another fields content  while at the same time keeping all other rows in the output result.  My test sample looks like this:

    proj | Job | elem | qty |

    3570 000  1         400

    3570 000  2         300

    3570 000  3         0

    3570 001  1         500

    3570 001  6         100

    3570 002  9         900

    What I need the output to look like is:

    proj | Job | elem | qty | Sum(1&2)|

    3570 000  1         400    700

    3570 000  2         300    700

    3570 000  3         0        NULL or 0

    3570 001  1         500     500

    3570 001  6         100     NULL or 0

    3570 002  9         900     NULL or 0

    My attempt at this was to use a join Similar to:

    Select a.proj, a.job, a.qty, sum(a.qty)

    from TBL1 a OUTER JOIN TBL1 b

    ON a.proj = b.proj AND a.job = b.job AND b.elem IN ('1','2')

    GROUP BY a.proj,a.job,a.elem

    This of course isn't happening any assistance pointing me in the right direction is appreciated.


    Sawyer

    Friday, September 13, 2013 3:34 PM

Answers

  • When I tried this I was still getting duplicate rows for groups that contained other elems other than 1's and 2's.  on groups that had only the 1's and/or 2's it did not duplicate... Then my boss came over and had me use a temp table and join the temp table to the original query.  Problem solved.

    Sawyer

    Friday, September 13, 2013 7:11 PM

All replies

  • If I understand what you are asking for, You could use a case statement to sum the fields. Something like:

    Select a.proj
      , a.job
      , a.elem
      , a.qty
      , (case when a.elem in (1,2) then a.qty else 0 end 
        + case when isnull(b.elem, 0) in (1,2) and a.elem in (1,2) then b.qty else 0 end) as QytSum
    from tbl1 a
      left outer join tbl1 b on b.proj = a.proj
        and b.job = a.job
        and b.elem != a.elem
    Group by a.proj
      , a.job
      , a.elem
      , a.qty

    The trick is in the join, you have to do a self join, but in order to prevent doubling you have to exclude the identical record. Once that's done, you can just add a.qty to b.qty when they are in elem 1 or 2. Then you group the results, and your done.  


    • Edited by Randall_M Friday, September 13, 2013 5:24 PM
    • Proposed as answer by Randall_M Friday, September 13, 2013 5:24 PM
    Friday, September 13, 2013 5:17 PM
  • I'm running version SQL Server 2008R2. I'm trying to add together two items from the same field but from different rows based on the another fields content  while at the same time keeping all other rows in the output result.  My test sample looks like this:

    proj | Job | elem | qty |

    3570 000  1         400

    3570 000  2         300

    3570 000  3         0

    3570 001  1         500

    3570 001  6         100

    3570 002  9         900

    What I need the output to look like is:

    proj | Job | elem | qty | Sum(1&2)|

    3570 000  1         400    700

    3570 000  2         300    700

    3570 000  3         0        NULL or 0

    3570 001  1         500     500

    3570 001  6         100     NULL or 0

    3570 002  9         900     NULL or 0

    Why 3570 001  1         500 return 500 and 3570 001  6         100 return null or 0? 

    Witch is the cryteria? 


    • Edited by DIEGOCTN Friday, September 13, 2013 5:53 PM
    Friday, September 13, 2013 5:53 PM
  • When I tried this I was still getting duplicate rows for groups that contained other elems other than 1's and 2's.  on groups that had only the 1's and/or 2's it did not duplicate... Then my boss came over and had me use a temp table and join the temp table to the original query.  Problem solved.

    Sawyer

    Friday, September 13, 2013 7:11 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Please learn how fields and columns are totally different concepts. If you had followed the rules:

    CREATE TABLE Projects
    (project_name CHAR(4) NOT NULL
     CHECK (project_name LIKE '[0-9][0-9][0-9][0-9]'), 
     job_name CHAR(3) NOT NULL
     CHECK (job_name LIKE '[0-9][0-9][0-9]'), 
     job_element CHAR(1) NOT NULL
     CHECK (job_element LIKE '[1-9]'), 
     PRIMARY KEY (project_name, job_name, job_element),
     foobar_qty INTEGER NOT NULL
     CHECK(foobar_qty >= 0)
    );

    INSERT INTO Projects
    VALUES
    ('3570', '000', '1', 400), 
    ('3570', '000', '2', 300), 
    ('3570', '000', '3', 0), 
    ('3570', '001', '1', 500), 
    ('3570', '001', '6', 100), 
    ('3570', '002', '9', 900);

    Did I guess right? Why did I have to do all this typing for you? 

    SELECT project_name, job_name, job_element,
            SUM(foobar_qty * (CASE WHEN job_element IN ('1','2') 
                    THEN 1 ELSE 0 END))
             OVER (PARTITION BY project_name, job_name)
            *  (CASE WHEN job_element IN ('1','2') 
                    THEN 1 ELSE 0 END)
            AS one_two_tot
      FROM Projects;

    1 3570 000 1 700
    2 3570 000 2 700
    3 3570 000 3 0
    4 3570 001 1 500
    5 3570 001 6 0
    6 3570 002 9 0

    You will want to look at this carefully; it is tricky. The CASE classifies the job_element, but it is used at two levels of aggregate. I am betting the optimizer will factor it out. The windowed SUM() is how we get the higher level of aggregation for the one_two_tot column. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 13, 2013 9:32 PM
  • You will want to look at this carefully; it is tricky. The CASE classifies the job_element, but it is used at two levels of aggregate. I am betting the optimizer will factor it out. The windowed SUM() is how we get the higher level of aggregation for the one_two_tot column. 

    In this case maybe could be a better solution this:

    with cte as(
    SELECT project_name, job_name, job_element, foobar_qty, SUM(FOOBAR_QTY) OVER (PARTITION BY PROJECT_NAME, job_name) as one_two_tot FROM PROJECTS)
    select project_name, job_name, job_element, foobar_qty, Iif(job_element in (1,2),one_two_tot,0) from cte
    
    I like a lot working with IIF...


    Friday, September 13, 2013 10:09 PM