Subject Re: SQL SYNTAX From Mervyn Bick <invalid@invalid.invalid> Date Mon, 19 Mar 2018 21:52:50 +0200 Newsgroups dbase.getting-started On 2018-03-18 9:27 PM, Mustansir Ghor wrote: > Dear All > > I have dbf table SUMMARY with item (c), qtyin (n), qtyout (n)  as fields. > > With have following command I am able to insert all  qtyin into the SUMMARY table > > INSERT INTO SUMMARY (ITEM, QTYIN) SELECT ITEM,SUM(QTY) FROM ARRIVALS GROUP BY ITEM > > Then for the same item record in the SUMMARY table I need to add qtyout  from SALES table. Please can anybody guide me what SQL syntax with which I shall be able to do this I don't know too much SQL (yet :-) ) and this may be doing things the hard way. I'm assuming that you are starting with an empty SUMMARY table.  If this is not so it's a whole new ball game.   All SQL tables need a primary key so that individual records can be identified when updating of data is needed.  As you are inserting grouped values from the ARRIVALS table there will only be one record for each item in the SUMMARY table.  This will serve to identify the record for the values from the SALES table. Your SQL INSERT command creates a new record in SUMMARY for each item in the ARRIVAL table and saves the QTYIN value.  So far, so good. :-) To add the QTYOUT values to the same records you need to use the SQL UPDATE command.  You also need a WHERE clause specifying the actual record in SUMMARY to update.   This can work IF the item is already in SUMMARY i.e  there was an arrival for the item.  The problem arises if you've sold an item that isn't already in the SUMMARY table.  You can't update a record that isn't there If the items in SALES are always already in SUMMARY the the following should do what you need. if file('summary.dbf')    drop table summary endif if not file('summary.dbf')     create table summary  (item numeric(10,2),qtyin numeric(10,2),qtyout numeric(10,2)) endif INSERT INTO SUMMARY (ITEM, QTYIN) SELECT ITEM,SUM(QTY) FROM ARRIVALS GROUP BY ITEM UPDATE SUMMARY SET QTYOUT = (SELECT SUM(SALES.QTY) FROM SALES WHERE SALES1.ITEM = SUMMARY.ITEM) If there is an item in the SALES table which is not already in the SUMMARY table it will be ignored without flagging an error. If there can be cases where items sold aren't already in the SUMMARY table the following should do the job.  For this you will need a table containing ALL the item numbers.  It doesn't matter if there are any other fields as you can simply add the fields QTYIN and QTYOUT, populate them, create SUMMARY.DBF and then remove the fields QTYIN and QTYOUT if file('summary.dbf')    drop table summary endif ALTER TABLE ITEMS ADD QTYIN NUMERIC(10,2), ADD QTYOUT NUMERIC(10,2) UPDATE ITEMS SET QTYIN = (SELECT SUM(QTY) FROM ARRIVALS WHERE ARRIVALS.ITEM = ITEMS.ITEM ) UPDATE ITEMS SET QTYOUT = (SELECT SUM(QTY) FROM SALES WHERE SALES.ITEM = ITEMS.ITEM ) SELECT ITEM,QTYIN,QTYOUT FROM ITEMS1 WHERE QTYIN <> 0 OR QTYOUT <> 0 SAVE TO SUMMARY ALTER TABLE ITEMS1 DROP QTYIN, DROP QTYOUT Not that the 'SAVE TO SUMMARY' must be on the same line as the 'SELECT' Mervyn.