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