Skip to content

Instantly share code, notes, and snippets.

@stwalkerster
Created December 19, 2016 13:17
Show Gist options
  • Save stwalkerster/ebba45965cb95cab66adf4ff8c9c80ab to your computer and use it in GitHub Desktop.
Save stwalkerster/ebba45965cb95cab66adf4ff8c9c80ab to your computer and use it in GitHub Desktop.
select
it.description
, coalesce(total.sum, 0) as sum
, coalesce(delta.delta, 0) as delta
from stwalkerster_ed_explore.incidenttype it
left join (
SELECT
i.type,
sum(delta) as sum
FROM stwalkerster_ed_explore.incident i
INNER JOIN stwalkerster_ed_explore.session s ON s.id = i.session
WHERE s.trip = %d
GROUP BY i.type
) total on total.type = it.id
left join (
SELECT si.type, si.delta
FROM stwalkerster_ed_explore.incident si
WHERE si.session = (
SELECT max(mi.session)
FROM stwalkerster_ed_explore.incident mi
INNER JOIN stwalkerster_ed_explore.session ms ON mi.session = ms.id
WHERE ms.trip = %d)
) delta on delta.type = it.id
where it.technical = %d
and (hidden = 0 or sum > 0)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment