Re: [SQL] sum gives different answer

Chairudin Sentosa wrote:

> Hi all,
>
> I have two SQL statements that I expect to give (0 rows) as output.
> However the first one with 'sum' does not do that.
> Why the first SQL statement gives (1 row) and empty?
> While the second SQL statement can give (0 row).
> I am using postgresql 6.4.2.
>
> First
> ------
> select pin, sum(duration) from tablename where date(start_time)=
> (select date 'today') group by pin;
>
> pin|sum
> ---+---
> |
> (1 row)
>
> Second
> ------
> select pin, duration from tablename where date(start_time)=
> (select date 'today') group by pin;
>
> pin|duration
> ---+--------
> (0 rows)
>
> Could anyone tell me why, please?
>
> Regards
> Chai

If I understund your problem ...

An aggregate function (like sum, min, max etc.) always return one row. If the
result of function is null, the aggregate result column is empty:

Example:

test=> create sequence s1;
CREATE
test=> CREATE TABLE test1 (id int4 DEFAULT nextval ('s1'), value float4,
explication text);
CREATE
test=> INSERT INTO test1 (value, explication) values (100, 'first');
INSERT 568765 1
test=> INSERT INTO test1 (value, explication) values (300, 'second');
INSERT 568766 1
test=> INSERT INTO test1 (value, explication) values (123, 'third');
INSERT 568767 1
test=> SELECT * FROM test1;
id|value|explication
--+-----+-----------
1| 100|first
2| 300|second
3| 123|third
(3 rows)

test=> SELECT sum(value) as sum_value FROM test1;
sum_value
---------
523
(1 row)

test=> SELECT sum(value) as sum_value FROM test1 WHERE value < 100;
sum_value
---------

(1 row)

test=> SELECT value FROM test1 WHERE value < 100;
value
-----
(0 rows)

If you want to use the resoult of one function like this in your applicatons,
you mast verify the content of aggregate column result.

Sorry for my english,

George

--
Best,
George Moga,
george(at)flex(dot)ro
Braila, ROMANIA