From: George Moga <george@flex.ro>
To: chairudin@prima.net.id, SQL PostgreSQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] sum gives different answer
Date: Fri, 12 Mar 1999 11:38:18 +0200
Message-ID: <36E8E089.2897F517@flex.ro> (raw)
References: <36E9213D.151B90E1@prima.net.id>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: george@flex.ro
Subject: Re: [SQL] sum gives different answer
In-Reply-To: <36E8E089.2897F517@flex.ro>
References: <36E9213D.151B90E1@prima.net.id> <36E8E089.2897F517@flex.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox