Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Question Unanswered: Need Help on SQL query about sum

    Hey guys,
    New to the site, new to writing SQL queries and have no background in this type of thing.
    But I've been googling my way to victory for the most part.

    I'm struggling on probably a very simple problem.
    I'd like to Sum the contents of a column, if a different value in the row is the same as in another... That was worded horribly - how about an illustration:

    Input Table:

    Code:
    Unit ID Value    orderid    orderdate
    1         20          12    20121121
    1         30          13    20121121
    1         20          14    20121121
    1         30          15    20121120
    1         10          16    20121121 
    2         51          17    20121113 
    3         50          18    20121114 
    4         20          19    20121112 
    4         30          20    20121112 
    4         10          21    20121112 
    4           5          22    20121112 
    4         50          23     20121112 
    5           5          25    20121110 
    5         50          26    20121111 
    6          5           27    20121112 
    6          3           28    20121112 
    6         50          29    20121114 
    7         35          30    20121112 
    7           4          31    20121112

    So basically I'm looking for an output when I run the script that shows
    aggregated of Value must >= 50 after sum up with ID.The flag indicator will tag with 'Y' if only when single unit >= 50 without sum with each other.
    The single unit >=50 will shown orderId and orderdate, not single unit >=50(sum up with other value) will shown nothing.
    Expected output :
    Code:
     
    1. ID Value                       Indicator flag OrderID orderDate
    2. 1  110(20+30+20+30+10)   N
    3. 2 51                             Y                 17 20121113 
    4. 3 50                             Y                 18 20121114 
    5. 4 115(20+30+10+5+50)      N 
    6. 5 55 (50+5)                    N 
    7. 6 58 (50+8)                    N

    And various other simplistic queries, but at this point I'm willing to admit that I haven't a clue what I'm doing.
    FYI, the platform I using is IBM DB2 I-series.

    For your convenience I have create the code below to generated the table for testing.You may can refer it.
    Code:
    CREATE TABLE #Test (
    	ID int,Value int,orderID int,Orderdate int)
    INSERT #Test VALUES
    (1,20,12,20121121),
    (1,30,13,20121121),
    (1,20,14,20121121),
    (1,30,15,20121120),
    (1,10,16,20121121),
    (2,51,17,20121113),
    (3,50,18,20121114),
    (4,20,19,20121112),
    (4,30,20,20121112),
    (4,10,21,20121112),
    (4,5,22,20121112),
    (4,50,23,20121112),
    (5,5,25,20121110),
    (5,50,26,20121111),
    (6,5,27,20121112),
    (6,3,28,20121112),
    (6,50,29,20121114),
    (7,35,30,20121112),
    (7,4,31,20121112)
    
    SELECT *
    FROM #Test
    Any help would be appreciated,
    Thanks!
    Last edited by yuexuan; 06-29-13 at 23:58. Reason: edit to the correct tags

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Here is a brute force query that should get you started:

    Code:
    ;with cte 
    as
    (
       select ID, COUNT(*) as cnt, SUM(value) as sumVal
        from #Test
          group by ID
     )
     Select c.ID, c.sumVal,
       case when c.cnt = 1 then 'Y' else 'N' end Flag,
       case when c.cnt = 1 then 
          (select t1.OrderID from #Test t1 where t1.ID = c.ID) else '' end OrderID,
       case when c.cnt = 1 then 
          (select t1.Orderdate from #Test t1 where t1.ID = c.ID) else '' end OrderDate
     
     from cte c
       where c.sumVal >= 50

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example tested on DB2 9.7 for Windows.
    Slight modifications might be neccesary on DB2 for i.

    Code:
    SELECT s.id
         , s.value
         , CASE s.cnt WHEN 1 THEN 'Y' ELSE 'N' END AS indicator_flag
    /*
     If you are using DB2 for i 7.1, then this expression may work.
         , SUBSTR('YN' , MIN(2 , s.cnt) , 1) AS indicator_flag
    */
         , COALESCE(CHAR(d.order_id)   , '') AS order_id
         , COALESCE(CHAR(d.order_date) , '') AS order_date
     FROM  (SELECT id
                 , SUM(value) AS value
                 , COUNT(*)   AS cnt
             FROM  #Test
             GROUP BY
                   id
             HAVING
                   SUM(value) >= 50
           ) AS s
     LEFT  OUTER JOIN
           #Test AS d
      ON   s.cnt = 1
       AND d.id  = s.id
    ;

  4. #4
    Join Date
    Jun 2013
    Posts
    3

    Smile

    Thanks for reply I have tried up with my own but not sure performance better than the solution above posted.

    Code:
    SELECT id,
    SUM(value) AS unit_total,
    CASE WHEN COUNT(ID) = 1
    AND SUM(value) >= 50
    THEN 'Y' ELSE 'N' END AS Flag,
    CASE WHEN (COUNT(ID) = 1 and SUM(Value) >=50) THEN MAX(orderid)
    ELSE 0 END as OrderID,
    CASE WHEN (COUNT(ID) = 1 and SUM(Value) >=50) THEN MAX(orderdate)
    ELSE 0 END as Orderdate
    FROM #Test
    GROUP BY id
    HAVING SUM(value) >= 50
    Since I only interested the single value >=50 and using HAVING sum(VALUE) >= 50,thus, orderid and orderdate only shown on that particular single value.
    Feel free to comment if can have any improvement.

    Thanks

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My two thoughts.

    (1) All "AND SUM(value) >= 50" in CASE expressions must be unnecessary.
    Because, groups which were not satisfied the condition were already excluded by HAVING clause.

    You may also want to see
    The sequence of the (hypothetical) operations is:

    1.FROM clause
    2.hierarchical-query clause
    3.WHERE clause
    4.GROUP BY clause
    5.HAVING clause
    6.SELECT clause
    7.ORDER BY clause
    8.FETCH FIRST clause
    in subselect

    (2) Although the results are same, I prefer to use COUNT(*) rather than COUNT(ID).
    Because, COUNT(*) is count of rows and COUNT(ID) is count of non-null IDs.
    And, you grouped by ID, then the check like "if ID is not null" which was implied in COUNT(ID) is useless.

    Please see more detail in COUNT

  6. #6
    Join Date
    Jun 2013
    Posts
    3

    Smile

    Quote Originally Posted by tonkuma View Post
    My two thoughts.

    (1) All "AND SUM(value) >= 50" in CASE expressions must be unnecessary.
    Because, groups which were not satisfied the condition were already excluded by HAVING clause.

    You may also want to see
    in subselect

    (2) Although the results are same, I prefer to use COUNT(*) rather than COUNT(ID).
    Because, COUNT(*) is count of rows and COUNT(ID) is count of non-null IDs.
    And, you grouped by ID, then the check like "if ID is not null" which was implied in COUNT(ID) is useless.

    Please see more detail in COUNT
    Hi tonkuma,thank for advice .Base on your advice my code can be improve as below:
    Code:
    SELECT id,
    SUM(value) AS unit_total,
    CASE WHEN COUNT(*) = 1
    AND SUM(value) >= 50
    THEN 'Y' ELSE 'N' END AS Flag,
    CASE WHEN (COUNT(*) = 1 ) THEN MAX(orderid)
    ELSE 0 END as OrderID,
    CASE WHEN (COUNT(*) = 1) THEN MAX(orderdate)
    ELSE 0 END as Orderdate
    FROM #Test
    GROUP BY id
    HAVING SUM(value) >= 50
    The id field is one of the key field in that table, it cannot be blanks,so don't worry it always non null.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by yuexuan View Post
    Hi tonkuma,thank for advice .Base on your advice my code can be improve as below:
    Code:
    SELECT id,
    SUM(value) AS unit_total,
    CASE WHEN COUNT(*) = 1
    AND SUM(value) >= 50
    THEN 'Y' ELSE 'N' END AS Flag,
    CASE WHEN (COUNT(*) = 1 ) THEN MAX(orderid)
    ELSE 0 END as OrderID,
    CASE WHEN (COUNT(*) = 1) THEN MAX(orderdate)
    ELSE 0 END as Orderdate
    FROM #Test
    GROUP BY id
    HAVING SUM(value) >= 50
    The id field is one of the key field in that table, it cannot be blanks,so don't worry it always non null.
    Another "AND SUM(value) >= 50" must be unnecessary, too.
    Code:
    SELECT id
         , SUM(value) AS unit_total
         , CASE
           WHEN COUNT(*) = 1
           THEN 'Y'
           ELSE 'N'
           END AS Flag
         , ...
    Did you considered my main point?
    (2) Although the results are same, I prefer to use COUNT(*) rather than COUNT(ID).
    Because, COUNT(*) is count of rows and COUNT(ID) is count of non-null IDs.
    And, you grouped by ID, then the check like "if ID is not null" which was implied in COUNT(ID) is useless.

Tags for this Thread

Posting Permissions

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