Re: [HACKERS] SUM() and GROUP BY

D'Arcy J.M. Cain wrote:

>
> Does this seem right?
>
> druid=> SELECT COUNT(*) FROM acctrans;
> count
> -----
>     0
> (1 row)
>
> druid=> SELECT client_id, SUM(tramount) FROM acctrans GROUP BY client_id;
> client_id|sum
> ---------+---
>          |
> (1 row)
>
> If there are no rows in the table then shouldn't the result be no rows
> when GROUP BY is used?  Further, What about this?
>
> druid=> SELECT SUM(tramount) FROM acctrans;
> sum
> ---
>
> (1 row)
>
> Shouldn't that be 0.00?
>
> What will the NUMERIC or DECIMAL types do in these situations?  It
> looks like INTEGER has the same behaviour as MONEY (which tramount is.)

    NUMERIC  and  DECIMAL  will  behave exactly as above, because
    it's the (irritating) correct behaviour. It is handled in the
    generic  grouping  and  aggregate  code  (in fact none of the
    aggregate functions  will  ever  be  called  if  there  isn't
    anything to count/average/sum).

    To  get  a zero count, you need a subselect in the targetlist
    (not implemented yet). Currently the only way to  simulate  a
    subselect in the targetlist is to put the count() into an SQL
    function that takes the  arguments  you  need  to  build  the
    qualification and returns the counted number.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #