Re(2): "select having" problem

Re(2): "select having" problem

Post by a_blo.. » Sun, 31 Dec 1899 09:00:00





> >> 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

 
 
 

Re(2): "select having" problem

Post by Jonathan Leffle » Sun, 31 Dec 1899 09:00:00





> > >> 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).

--

Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>

 
 
 

1. Re(4): "select having" problem

That's alright sweetie pie.

I've never been arrested for it Dude!

Not really. Here are the options:

select ord_id, count(*)

which groups by ord_id then counts the number of rows in each group.

select ord_id, count(ship_num)

which groups by ord_id then counts the number of ship_num's in each group. So this is the same as count(*).

select ord_id, count(unique ship_num)

which groups by ord_id then counts the number of unique ship_num's in each group. So if you have two of the same ship_num's in the same group, it will only get counted once.

Try these selects in any database:

select colno, count(*)
from syscolumns
group by 1
order by 1
;
select colno, count(collength)
from syscolumns
group by 1
order by 1
;
select colno, count(unique collength)
from syscolumns
group by 1
order by 1

Have another beer Dude, you have exhausted my brain cell now. Happy BDay!

AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com

2. Deadlock handling problem

3. "select having" problem

4. Using C with Informix-online

5. Having "HAVING" problems with GROUP BY.

6. Using DBGrids for Unbound Data ?

7. Having problems with "Insert Into" command

8. eTrust/ Short Contract/ IN

9. max of ("...","...","..")

10. "Select * from Insert Into" Locking Problem

11. Select MIN() "problem"

12. "Select Into #Iemp" problem

13. Problem with "Join"-ing in a select statement