pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (6+ messages)  latest in thread

Message-ID: <36E8E089.2897F517@flex.ro>
Permalink:  ../36E8E089.2897F517@flex.ro/
Also on:    postgresql.org/message-id/36E8E089.2897F517@flex.ro

 · 

reply

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