Printable View
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
[QUOTE=bhutyaa;21941]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[/QUOTE]
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
hi thnx u 4 ur solution.
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.
Hi....
This is based on the hierarchi of evaluation of the queries that has been entered.
in the first one
[B]select sum(a*b ) from tabABC group by a,b[/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
[B]select sum(a) * sum(b) from tabABC
group by a,b[/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