List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 4 2000 11:48pm
Subject:problem with COUNT() in arithmetic operations
View as plain text  
>>>>> "Nicolas" == Nicolas Prade <prade@stripped>
> writes:

Nicolas> Hello,
Nicolas> in the following request, the first column of the result should not be null.
Nicolas> Is there an obvious explanation I passed by ?
Nicolas> Is it a side effect of a known bug ?
Nicolas> I already searched the docs and the list, could not find the answer...
Nicolas> Is it a new bug ?

Nicolas> (COUNT(id)*end-COUNT(id)*beginning)/(nb_views_required*NOW()-nb_views_requir
Nicolas> ed*beginning) as a_divided_by_b, (COUNT(id)*end-COUNT(id)*beginning) as a,
Nicolas> (nb_views_required*NOW()-nb_views_required*beginning) as b FROM campaign
Nicolas> +----------------+---------+----------+
Nicolas> | a_divided_by_b | a       | b        |
Nicolas> +----------------+---------+----------+
Nicolas> |           NULL | 2000000 | 97802000 |
Nicolas> +----------------+---------+----------+

Nicolas> I run the 2 following configs (same problem width both) :
Nicolas> -- Ver 6.9 Distrib 3.21.33, for sun-solaris2.6 on sparc
Nicolas> SunOS jason 5.6 Generic_105181-11 sun4u sparc SUNW,Ultra-4 (Ultra 450)
Nicolas> -- Ver 7.11 Distrib 3.22.25, for pc-solaris2.7 on i386
Nicolas> SunOS hermes 5.7 Generic_106542-05 i86pc i386 i86pc (Dell 2300)


If you use COUNT(..) you MUST use all other column names in the GROUP
BY statement that is not inside a summary function.  If you don't do
this, the columns may contain values from any row in the table.

In other words, add a GROUP BY and try again

problem with COUNT() in arithmetic operationsNicolas Prade4 Jan
  • problem with COUNT() in arithmetic operationsMichael Widenius5 Jan