On Fri, Jun 27, 2003 at 09:05:48AM -0400, Moulder, Glen wrote: > G'day all, > > Having trouble with SUM, could use a hand. I inherited a table, Funds_Recd that > looks like this: > > ID Job Sub Task Amt > 1 A01 A 19 10,001.00 > 102 B53 A 201 79.47 > 213 X99 L 88 38,289.02 > 284 A01 A 19 126.98 > > I do not have ownership and *MAY NOT* change the structure. > > I've another table, Jobs, to which the Funds_Recd recs must be related: > > ID Job Sub Task > 87 A01 A 19 > 929 B53 A 201 > 998 X99 L 88 > > This query works -- > > SELECT Jobs.Job, Jobs.Sub, Jobs.Task, Funds_Recd.Amt > FROM Jobs, Funds_Recd > WHERE Jobs.Job=Funds_Recd.Job and Jobs.Sub=Funds_Recd.Sub and > Jobs.Task=Funds_Recd.Task > ORDER BY Jobs.Job; > > but produces a list of jobs with individual rows for the various funds amounts. I'm > trying to get just a total of the funds amount for a Job, Sub and Task like this -- > > SELECT Jobs.Job, Jobs.Sub, Jobs.Task, sum(Funds_Recd.Amt) AS Amount > FROM Jobs, Funds_Recd > WHERE Jobs.Job=Funds_Recd.Job and Jobs.Sub=Funds_Recd.Sub and > Jobs.Task=Funds_Recd.Task > ORDER BY Jobs.Job; > > but this produces an error telling me I'm not using Jobs.ID in the aggregate > function. > > Is there a way to generate this query without adding the Jobs.ID column to the > Funds_Recd table? >
I guess you want to use a GROUP BY because the SUM makes no sense without it. You seem to want results for unique combinations of Job, Sub and Task. This means: GROUP BY Jobs.Job, Jobs.Sub, Jobs.Task; Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
