I have the following SQL:
select
inv.salesman_id,
(select salesman_goals.goal from salesman_goals
where salesman_goals.salesman_id = inv.salesman_id
and salesman_goals.group_id = g.group_id
and salesman_goals.subgroup_id = sg.subgroup_id
and salesman_goals.variation_id = v.variation_id)
as goal,
sum(i.quantity) as qnt
from invoiceitem i
inner join invoice inv on inv.invoice_id = i.invoice_id
inner join product p on p.product_id = i.product_id
left join groups g on g.group_id = p.group_id
left join subgroup sg on sg.group_id = g.group_id and sg.subgroup_id = p.subgroup_id
left join variation v on v.group_id = sg.group_id and v.subgroup_id = sg.subgroup_id and v.variation_id = p.variation_id
group by
1,2
which returns three columns, the first one is the salesman id, the second is a sub select to get the sales quantity goal, and the third is the actual sales quantity. Even grouping by the first and second columns, firebird throws an error when executing the query:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).
What's the reason for this?
There is a column "in the select list (not contained in either an aggregate function or the GROUP BY clause)". Namely each column you mention in your subselect other than inv.salesman_id. Such a column has many values per group. When there is a GROUP BY (or just a HAVING, implicitly grouping by all columns) a SELECT clause returns one row per group. There is no single value to return. So you want (as you put in an answer yourself):
group by
inv.salesman_id,
g.group_id,
sg.subgroup_id,
v.variation_id