Dave, You can do this in one query but you need to left join on a derived table, like this:
SELECT t1.item_id, t1.item_descp, t1.commodity_code, t1.on_hand_qty, t2.items_on_hand FROM dbo.tbl_elec_req AS t1 LEFT JOIN (SELECT item_id as key, sum(on_hand_qty) AS items_on_hand from items group by item_id) AS t2 ON t1.item_id=t2.key WHERE t2.key IS NOT NULL ~Dina ----- Original Message ----- From: "Bosky, Dave" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Monday, March 24, 2003 11:21 AM Subject: Sum of unique items > Question SQL Gurus. > > I need to obtain the total of all 'on_hand_qty' for each unique item_id. > Can this be done or do I need additional queries? > > SELECT item_id, item_descp, commodity_code, on_hand_qty > FROM dbo.tbl_elec_req > > Regards, > > Dave Bosky > Sr. Multimedia Web Designer > Horry Telephone Cooperative, Inc. > office: (843)369-8613 > [EMAIL PROTECTED] > > > > HTC Disclaimer: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
