Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    16

    Unanswered: How to eliminate 'unwanted' rows

    I am running the below query and this row among others should not display based on my query.

    select b.custid,a.customer,a.totalamt,b.amount, sum(b.amount) over (partition by b.custid)
    from supplies a, purchase b

    where b.custid in ('1023','2478')
    and a.custid=b.custid
    group by b.custid,a.customer,b.amount,a.totalamt
    having sum(amount) <> totalamt

    custid customer totalamt amount sum (amount)
    2478 APPBOOK 366.00 33.0000 366.0000
    2478 APPBOOK 366.00 333.0000 366.0000

    This should not be shown, this needs to be eliminated because 333.33 = 366.0000 which is the same amount as 366.0000
    Last edited by peadove; 10-03-13 at 21:08.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Would any of b.amount and a.totalamt happen to be of float or double data type?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2013
    Posts
    16
    n_I, thanks for taking the time to reply.

    b.amount and a.totalamt have the same data type. The type is decimal.

    By 'it does not work' I meant unintended rows are been extracted. For example the row (as shown below) should not be included in the results since the sum of (33+333 = 366) is equal to amtrpt which is also 366. I want to filter out rows where the sum(amount) is not the same as the amtrpt.

    custid customer totalamt amount sum (amount)
    2478 APPBOOK 366.00 33.0000 366.0000
    2478 APPBOOK 366.00 333.0000 366.0000

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select a.custid, a.customer, a.totalamnt, b.amount, bg.amount_sum
    from supplies a
    join (
    select custid, sum(amount) amount_sum
    from purchase
    group by custid
    ) bg on bg.custid=a.custid and bg.amount_sum<>a.totalamnt
    join purchase b on b.custid=a.custid
    where b.custid in ('1023','2478')
    Regards,
    Mark.

  5. #5
    Join Date
    Jan 2013
    Posts
    16
    Mark, that works like a charm. Thanks a bunch. If you explain the rational behind your logic. This will help me understand better.

    Again, thanks your help.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In your original statement you are ultimately grouping by amount, so sum(amount) within that group will obviously be equal to amount itself, not sum(b.amount) over (partition by b.custid), which is grouped by custid.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This may be another solution.

    Code:
    SELECT a.custid
         , a.customer
         , a.totalamt
         , b.sum_amount
         , b.amount
     FROM  supplies a
     INNER JOIN
           (SELECT b.*
                 , SUM(amount) OVER(PARTITION BY custid) AS sum_amount
             FROM  purchase b
             WHERE custid IN ('1023' , '2478')
           ) b
      ON   b.custid = a.custid
       AND b.sum_amount <> a.totalamt
     WHERE a.custid IN ('1023' , '2478')
     ORDER BY
           custid
    ;

  8. #8
    Join Date
    Jan 2013
    Posts
    16
    Thanks everyone for taking the time to respond. I have a clear understanding now of what is going on.

Posting Permissions

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