Row count

[gotmilk2]

I want to update rows in a table that have foe than one location associated with it but does not include certain locations. I have tried the following:

group by may_po_nbr, loc_nbr
having rowcount_big() > 1

and rowcount_big() > 1
group by may_po_nbr, loc_nbr

Both bring back even those with one location associated with it.

 

[AlexCuse]

Not sure what you mean by " foe than one location "

Also, I don't think you are using ROWCOUNT_BIG() correctly. Can you post your query?

Alex




Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[SQLDenis]

rowcount_big() is a replacement for @@Rowcount and will always return > 1 in your case

what you need is count(*)

group by may_po_nbr, loc_nbr
having count(*) > 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gotmilk2]

Let me give a little more information:

The SQL is:
Select distinct px.may_po_nbr From may_to_fed_po px
group by px.may_po_nbr, px.loc_nbr having count(*) > 1

This return PO numbers 165571 and 195597. I did not expect that. I would think neither of the PO numbers would be returned.

Data example:

PO_NBR SKU_NBR LOC_NBR
------- -------- -------
165571 16528069 60
165571 16528051 60
165571 16528044 60
165597 16528069 185
165597 16528051 185
165597 16528044 185

 

[AlexCuse]

Because you are not grouping by SKU_NBR, you are seeing multiple rows for the two PO_NBR entries. If you group by SKU_NBR you will see the results you want, but I'm not sure they are in fact the results you want.

What is the goal of your update query?


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.