You are here: Re: Compare record count in table1 to qty in table2? « MsSQL Server « IT news, forums, messages
Re: Compare record count in table1 to qty in table2?

Posted by rdraider on 10/13/06 18:20

I got an error:
"An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause or a select list, and the column being
aggregated is an outer reference."

The script from Hugo worked well. Thanks for your help.


"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:KNDWg.991$zy2.943@tornado.socal.rr.com...
> rdraider wrote:
>
>> We have an inventory table (Items) that contains item_no and qty_on_hand
>> fields.
>> Another table (Item_Serial) contains serial numbers for any item that has
>> serial numbers.
>> If an item has 10 qty_on_hand, it should have 10 records in Item_Serial,
>> one unique serial number for each item.
>>
>> I am trying to find items where the number of serial numbers does not
>> equal the qty_on_hand.
>> Here is a query I'm trying to use and, of course, it does not work:
>>
>> select Items.item_no, Items.qty_on_hand
>> from Items inner join Item_Serial on Item_Serial.item_no =
>> Items.item_no
>> where Items.qty_on_hand <> count(Item_Serial.item_no)
>
> I believe this will work:
>
> select Items.item_no, Items.qty_on_hand,
> coalesce(sum(Item_Serial.item_no),0) serial_qty_on_hand
> from Items
> left join Item_Serial on Item_Serial.item_no = Items.item_no
> where Items.qty_on_hand <> coalesce(sum(Item_Serial.item_no),0)
>

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация