Subject Re: sql code not working From Mervyn Bick <invalid@invalid.invalid> Date Fri, 15 Jun 2018 17:48:29 +0200 Newsgroups dbase.getting-started On 2018-06-15 4:42 PM, Charlie wrote: > Hi Mervyn... OK I am still struggling with this but do understand more. > > I used the filter only because the sql statement didn't seem to work. > >   sql = 'select talname,sum(qty) as cnt, sum(qty*cost) as tcost, > sum(qty*sell) as tsell from taldata group by talname' > > But possibly you didn't know exactly what I was doing.  And I didn't understamd tje group by name is actually a filter of it's own (I think) > > Talname is actually a variable so I changed that in the sql statement to lname which should work!!! > > I am not using this in the grid but instead to three entryfields which I linked to  the  appropriate fields in form.taldata2. > > I must be doing something wrong as I get blank data when I have this set up in each entryfield. > > At that point before I understood the 'group by' I assumed I needed a filter. > > Your first suggestion seems to be the easiest but I don't know what is wrong and why I'm not seeing the data in each entryfield. > > Hope this makes sense.  Please let me know if there is a resolve! If lname is the field name in the taldata table then    sql = 'select lname,sum(qty) as cnt, sum(qty*cost) as tcost, sum(qty*sell) as tsell from taldata group by lname' That will create a rowset that has a single record for each name in the lname field.  Each record has the fields lname, cnt, tcost and tsell with the appropriate total values in the fields cnt, tcost and tsell fields for each vendor.  It is a standard rowset with nothing special about it and you treat it just like any other rowset. If you datalink entryfields to each field you will see the data for the selected record.  You can either use oRowset.applyLocate() to move the row pointer to the record you want or you can set the rowset's filter form.taldata1.rowset.applyLocate([lname = ']+talname+[']) or form.taldata1.rowset.filter = [lname = ']+talname+['] Whenever fieldnames and aggregating functions such as count() or sum() are used together in a select statement the group by clause must be used and it must include all the selected fieldnames in the list.  This is a SQL requirement. Mervyn.