SEO

#1 (**permalink**) 02-27-2008, 02:47 AM

Hi I m using DB2 8.2 on win XP

I am surprised to see the functioning of SUM() function

db2 => select sum(100),count(*) from sysibm.sysdummy1

1 2
----------- -----------
100 1

1 record(s) selected.

Can someone tell me the cause of such behaviour?
Thanks in advance
mailar

#2 (**permalink**) 02-27-2008, 02:47 AM

<mailar@gmail.com> wrote in message
news:1107245995.198340.51040@c13g2000cwb.googlegro ups.com...
> Hi I m using DB2 8.2 on win XP
>
> I am surprised to see the functioning of SUM() function
>
> db2 => select sum(100),count(*) from sysibm.sysdummy1
>
> 1 2
> ----------- -----------
> 100 1
>
> 1 record(s) selected.
>
>
> Can someone tell me the cause of such behaviour?
> Thanks in advance
> mailar
>
There is one row in sysibm.sysdummy1 (this accounts for the count(*))

Taking the sum of a literal will equal the literal.

What is the question?

#3 (**permalink**) 02-27-2008, 02:47 AM

I am surprised to see this kind of behaviour of SUM() function

My master_tab table has 7 records

db2 => select sum(100),count(*) from master_tab

1 2
----------- -----------
700 7

1 record(s) selected.
So, can someone tell me how does the SUM() function actualy work?

#4 (**permalink**) 02-27-2008, 02:47 AM

mailar@gmail.com wrote:
> So, can someone tell me how does the SUM() function actualy work?

It works exactly as it's expected to: it sums the given argument over
the grouped rows. 100 * 7 = 700 (afair, at least

)

Where's the problem? What did you expect as the result of your queries?
--
Please compose your messages as plaintext:
http://www.netby.dk/Oest/Europa-Alle/vermeer/plain.html
And do not send MS Office attachments:
http://www.goldmark.org/netrants/no-word/attach.html

#5 (**permalink**) 02-27-2008, 02:47 AM

As far as I know, SUM() works as mentioned below

The SUM function returns the sum of a set of numbers.

The argument values must be numbers (built-in types only) and their sum
must be within the range of the data type of the result.

The data type of the result is the same as the data type of the
argument values except that:

The result is a large integer if the argument values are small
integers.
The result is double-precision floating point if the argument values
are single-precision floating point.

This is an abstract from DB2 Information center

But how does SUM() take a literal value and multiply it with number of
rows in that table ....etc?

#6 (**permalink**) 02-27-2008, 02:47 AM

mailar@gmail.com wrote:
> As far as I know, SUM() works as mentioned below
>
> The SUM function returns the sum of a set of numbers.
>
> The argument values must be numbers (built-in types only) and their
> sum must be within the range of the data type of the result.
>
> The data type of the result is the same as the data type of the
> argument values except that:
>
> The result is a large integer if the argument values are small
> integers.
> The result is double-precision floating point if the argument values
> are single-precision floating point.
>
> This is an abstract from DB2 Information center
>
> But how does SUM() take a literal value and multiply it with number of
> rows in that table ....etc?

I think you're assuming that Sum() works as it does in a spreadsheet...

Sum(1) = 1
Sum(1, 2) = 3
Sum(1, 2, 3) = 6

In a database aggregate functions work against *rows*. If the query uses a
Group By clause then the aggregations are over the rows "per group". If there
is no Group By clause then the aggregations are over the entire Result Set.

#7 (**permalink**) 02-27-2008, 02:48 AM

mailar@gmail.com wrote:

> As far as I know, SUM() works as mentioned below
>
> The SUM function returns the sum of a set of numbers.
>
> The argument values must be numbers (built-in types only) and their sum
> must be within the range of the data type of the result.
>
> The data type of the result is the same as the data type of the
> argument values except that:
>
> The result is a large integer if the argument values are small
> integers.
> The result is double-precision floating point if the argument values
> are single-precision floating point.
>
> This is an abstract from DB2 Information center
>
> But how does SUM() take a literal value and multiply it with number of
> rows in that table ....etc?

SUM doesn't multiply anything. It just takes 100 for each of the rows (7 in
your case) and sums those 100s up. Your query is logically the same like
this one:

SELECT SUM(col1), count(*)
FROM ( SELECT 100
FROM master_tab ) AS t(col1)

Whether you specify a column name as argument or a literal doesn't matter.
DB2 (and any other SQL engine) will take the value it finds in each row -
either a column value or the literal - and sum it up.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

What does this mean?