As mentioned by @siggemannen, there is inconsistency between your insert statements and your initial "InvoiceLine" table data.
In the below I'm assuming your insert statements are correct with "Ticketing" for id "007" and "008".
WITH a AS (
SELECT InvoiceId, COUNT(DISTINCT Activity) AS ActivityCount
FROM InvoiceLine
GROUP BY InvoiceId
),
b AS (
SELECT
il.Activity,
SUM(il.Amount) AS Amount,
i.Pax,
a.ActivityCount
FROM Invoice i
JOIN InvoiceLine il ON i.Id = il.InvoiceId
JOIN a ON i.Id = a.InvoiceId
GROUP BY il.Activity, i.Pax, a.ActivityCount
)
SELECT
Activity,
SUM(Amount) AS Amount,
SUM(Pax) AS Pax,
SUM(CAST(Pax AS FLOAT) / ActivityCount) AS GlobalPax
FROM b
GROUP BY Activity
Output:
Activity | Amount | Pax | GlobalPax |
---|---|---|---|
Insurance | 6.6000 | 2 | 0.666666666666667 |
Reservation | 18.0000 | 4 | 1.66666666666667 |
Ticketing | 260.0000 | 14 | 10.3333333333333 |