Of cousre it should be
Group by 1, 2
Fingers too quick - sorry
Marcin
------ Wiadomość oryginalna ------
Od: "'Marcin Bury' [email protected] [firebird-support]"
<[email protected]>
Do: [email protected]; [email protected]
Wysłano: 18.01.2016 09:51:37
Temat: Re: [firebird-support] 'Invalid expression' error in GROUP BY
when field based on a SQL statement is used in SELECT field list
Steve
Try :
Grou by 1,2
instead of GROUP BY ReceiptID, Service
Marcin
------ Wiadomość oryginalna ------
Od: "[email protected] [firebird-support]"
<[email protected]>
Do: [email protected]
Wysłano: 18.01.2016 09:50:08
Temat: [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
& nbsp; 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?
