Re: Re(2): "select having" problem
---- you wrote:
> a_blonde@mindless.com (08.03.2000 12:43):
> >> But when I try to further filter the data with a having clause all I get is
> >> a syntax error
> >>
> >> select ord_id, count(unique ship_num)
> >> from ord_l
> >> where ord_id < 1000
> >> group by ord_id
> >> having count(unique ship_num) > 1>
> Hi!
>
> As far as I remember, using "count(<any_column_you_want>)" is useless by default,
> since the number of rows is the same, regardless of the column you count them on.
> Simply replace with "count(*)", both in SELECT and HAVING.
No, the point failed to make contact with you Dude.
count(*) will give you the total rows in each group of ord_id. count(unique ship_num) will give you the number of different ship_num's in each group of ord_id.
A very different result.
AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com
a_blonde@mindless.com wrote:
> ---- you wrote:
> > a_blonde@mindless.com (08.03.2000 12:43):
> > >> But when I try to further filter the data with a having clause all I get is
> > >> a syntax error
> > >>
> > >> select ord_id, count(unique ship_num)
> > >> from ord_l
> > >> where ord_id < 1000
> > >> group by ord_id
> > >> having count(unique ship_num) > 1> >
> > As far as I remember, using "count(<any_column_you_want>)" is useless by default,
> > since the number of rows is the same, regardless of the column you count them on.
> > Simply replace with "count(*)", both in SELECT and HAVING.
>
> No, the point failed to make contact with you Dude.
>
> count(*) will give you the total rows in each group of ord_id. count(unique ship_num) will give you the number of different ship_num's in each group of ord_id.
>
> A very different result.
And count(somecolumn) only counts the non-null values in that column,
which is different again from both count(*) and count(unique somecolum).
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>