Re(4): "select having" problem

Re(4): "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.

> Ok, sweetheart, I missed the objective; my BDay today and started with the bear
> a little early.

That's alright sweetie pie.

Quote:> I might miss it again, but asking never hurts. So: isn't UNIQUE
> inside the COUNT illegal?

I've never been arrested for it Dude!

Quote:> I mean, the query translates to something like: "determine
> the groups by ord_id, then determine the sub-groups by ship_num, then count, then filter"

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

Quote:> So probably a sub-query or "group by ord_id, ship_num" should do the trick.

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

 
 
 

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

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

2. Reasons for binary sort-order

3. "select having" problem

4. Database design assistance

5. Having "HAVING" problems with GROUP BY.

6. Using HTTP QUERIES

7. Having problems with "Insert Into" command

8. 416 error

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