Results 1 to 5 of 5

Thread: Sum giving different results

  1. #1
    Junior Member
    Join Date
    Nov 2007
    Answers
    1

    Sum giving different results

    if you sum one field w/o a group by and if the sum of the fields taken seperatly and a group by statement added it gives different results
    like ::

    select sum(a*b ) from tabABC
    and select sum(a) * sum(b) from tabABC
    group by a,b

    fetches different results.
    why is that happening cd anybody explain


  2. #2
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    Re: Sum giving different results

    Quote Originally Posted by bhutyaa View Post
    if you sum one field w/o a group by and if the sum of the fields taken seperatly and a group by statement added it gives different results
    like ::

    select sum(a*b ) from tabABC
    and select sum(a) * sum(b) from tabABC
    group by a,b

    fetches different results.
    why is that happening cd anybody explain
    Dear Bhutyaa,

    Both the case is different

    Suppose you have the following two columns:

    a b
    ___
    1 2
    3 4

    Now consider the first case:

    select sum(a*b ) from tabABC

    This will first take the product of a and b resulting in the following:
    1*2=2
    3*4=12

    then it will sum up the twovalues resulting in:
    Value=14

    Now consider the second case:

    First take

    select sum(a) * sum(b) from A

    The above query will first take sum total of column B resulting in:
    Value SUM(B)=6
    then in the same manner sum total of column A will result in:
    Value SUM(A)=4

    Now the product of SUM(a) and SUM(b) will be taken resulting in :
    SUM(a)*SUM(b)=24

    Now as you are using a group by,

    select sum(a) * sum(b) from tabABC
    group by a,b

    will result in

    SUM(a)*SUM(b)
    _____________
    2
    12

    Now if you need the same result as the first query,
    You have to write the query as under:

    select sum(sum(a) * sum(b)) from tabABC
    group by a,b

    I hope this will help you in giving a brief idea.

    Have a pleasant time


  3. #3
    Junior Member
    Join Date
    Nov 2007
    Answers
    1

    Re: Sum giving different results

    hi thnx u 4 ur solution.


  4. #4
    Junior Member
    Join Date
    Dec 2007
    Answers
    4

    Re: Sum giving different results

    Hi,

    Say you have this data in a table

    ABC:

    NO NO1
    2a 3b
    5a 9b

    now, check out:

    sum(no*no1)=(6ab+45ab)=51ab
    sum(no)*sum(no1)=(7a*12b)=84ab

    Bye.


  5. #5
    Junior Member
    Join Date
    Nov 2007
    Answers
    8

    Re: Sum giving different results

    Hi....

    This is based on the hierarchi of evaluation of the queries that has been entered.

    in the first one
    select sum(a*b ) from tabABC group by a,b

    the grouping is done then the values of the column A and column B are multiplied and then the value of the multiples are added.

    where as in the second one

    select sum(a) * sum(b) from tabABC
    group by a,b


    the sum of the value of the column A and the sum of value of column B is taken and after that the multiplication is done that is why there is a difference in the values.

    In the first one the expresiion within the bracket is evaluated and then the addition is done where as

    in the second one the sum that is the function is evaluated first and then the multiplication is done.

    I think this explanation would help you understand your doubt.

    regards,
    Saran


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact