pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [SQL] sum gives different answer
6+ messages / 4 participants
[nested] [flat]

* Re: [SQL] sum gives different answer
@ 1999-03-12 09:38  George Moga <george@flex.ro>
  parent: Chairudin Sentosa <chairudin@prima.net.id>
  0 siblings, 0 replies; 6+ messages in thread

From: George Moga @ 1999-03-12 09:38 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* sum gives different answer
@ 1999-03-12 11:28  Chairudin Sentosa <chairudin@prima.net.id>
  0 siblings, 0 replies; 6+ messages in thread

From: Chairudin Sentosa @ 1999-03-12 11:28 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* sum gives different answer
@ 1999-03-12 12:24  Chairudin Sentosa <chairudin@prima.net.id>
  0 siblings, 0 replies; 6+ messages in thread

From: Chairudin Sentosa @ 1999-03-12 12:24 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* sum gives different answer
@ 1999-03-12 14:14  Chairudin Sentosa <chairudin@prima.net.id>
  0 siblings, 1 reply; 6+ messages in thread

From: Chairudin Sentosa @ 1999-03-12 14:14 UTC (permalink / raw)
  To: pgsql-sql

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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: sum gives different answer
@ 1999-03-14 18:04  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 1999-03-14 18:04 UTC (permalink / raw)
  To: pgsql-sql@hub.org

Chairudin Sentosa <chairudin@prima.net.id> writes:
> I have two SQL statements that I expect to give (0 rows) as output.
> However the first one with 'sum' does not do that.

> select pin, sum(duration) from tablename where date(start_time)=
> (select date 'today') group by pin;
> pin|sum
> - ---+---
>    |
> (1 row)

> select pin, duration from tablename where date(start_time)=
> (select date 'today') group by pin;
> pin|duration
> - ---+--------
> (0 rows)

As George Moga pointed out, SUM() applied to an empty collection of tuples
still gives a result (one tuple containing NULL).  Aggregates in general
will give a result of some kind for an empty input collection --- for
example, you'd surely want COUNT() to return 0 not nothing.

However what you're talking about here is different: there are no groups
in the result, therefore SUM() should never have been applied at all,
not applied once to an empty set of tuples.  I'm firmly of the opinion
that the first example above is a bug.  The hackers list has been around
on this question a couple of times, and there are some folks who claim
that the current behavior is OK, but I'm at a loss to follow their
reasoning.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Re: sum gives different answer
@ 1999-03-14 18:10  Clark Evans <clark.evans@manhattanproject.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Clark Evans @ 1999-03-14 18:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org

Chairudin Sentosa <chairudin@prima.net.id> writes:
> select pin, sum(duration) from tablename where date(start_time)=
> (select date 'today') group by pin;
> pin|sum
> - ---+---
>    |
> (1 row)

Definately a bug.

Tom Lane wrote:
> SUM() should never have been applied at all

Yep.

Clark Evans



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1999-03-14 18:10 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-12 11:28 sum gives different answer Chairudin Sentosa <chairudin@prima.net.id>
1999-03-12 12:24 sum gives different answer Chairudin Sentosa <chairudin@prima.net.id>
1999-03-12 14:14 sum gives different answer Chairudin Sentosa <chairudin@prima.net.id>
1999-03-12 09:38 ` George Moga <george@flex.ro>
1999-03-14 18:04 Re: sum gives different answer Tom Lane <tgl@sss.pgh.pa.us>
1999-03-14 18:10 ` Clark Evans <clark.evans@manhattanproject.com>

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