79224176

Date: 2024-11-25 18:20:04
Score: 1
Natty:
Report link

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

fiddle

Reasons:
  • Long answer (-0.5):
  • Has code block (-0.5):
  • User mentioned (1): @siggemannen
  • Filler text (0.5): 66666666666666
  • Filler text (0): 6666666666666
  • Filler text (0): 3333333333333
  • Low reputation (0.5):
Posted by: keithwalsh