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@flex.ro
Braila, ROMANIA
