May be this is what you want: SELECT ReceiptID, (SELECT FIRST 1 S.Service FROM ReceiptLine RLF JOIN Service S ON (S.ServiceID = RLF.ServiceID) WHERE RLF.ReceiptLineID = MIN(RL.ReceiptLineID) ) AS Service, SUM(Qty*Cost) Price FROM ReceiptLine RL GROUP BY ReceiptID
Regards, Arno Brinkman ABVisie From: mailto:[email protected] Sent: Monday, January 18, 2016 9:50 AM To: [email protected] Subject: [firebird-support] 'Invalid expression' error in GROUP BY when field based on a SQL statement is used in SELECT field list This error: "Dynamic SQL Error SQL error code = -104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)" is returned by the following SQL: SELECT ReceiptID, ( SELECT FIRST 1 S.Service FROM Service S WHERE (S.ServiceID = RL.ServiceID) ORDER BY RL.ReceiptLineID ) AS Service, SUM(Qty*Cost) Price FROM ReceiptLine RL GROUP BY ReceiptID, Service Additional information: The table ReceiptLine is a detail (as in master-detail) table that stores the line items relating to a purchase, with the fields ReceiptID and ServiceID being foreign keys. The Service table stores product/service names. Using the ReceiptLine and Service tables, the SQL needs to provide the total of each receipt with only the first service name from the set of line items. Is there an alternative way, using SQL only, to achieve the desired result?
