Advertisement
Guest User

Untitled

a guest
Jul 16th, 2019
103
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.84 KB | None | 0 0
  1. SELECT runway_sign as Landebahn, [Anzahl gelandet], [Anzahl gestartet] from
  2. (
  3. SELECT runway_sign, COUNT(runway_sign) as [Anzahl gelandet], 0 as [Anzahl gestartet] FROM tbl_arriving_planes
  4. GROUP BY runway_sign
  5. ) as b
  6. UNION
  7. SELECT runway_sign as Landebahn, [Anzahl gelandet] ,[Anzahl gestartet] from
  8. (
  9. SELECT runway_sign, 0 as [Anzahl gelandet], COUNT(runway_sign) as [Anzahl gestartet] FROM tbl_leaving_planes
  10. GROUP BY runway_sign
  11. ) as a
  12.  
  13. runway_sign | number of arrived planes | number of leaving planes
  14.  
  15. SELECT runway_sign as Landebahn,
  16. SUM([Anzahl gelandet]),
  17. SUM([Anzahl gestartet])
  18. FROM ((SELECT runway_sign, COUNT(runway_sign) as [Anzahl gelandet], 0 as [Anzahl gestartet]
  19. FROM tbl_arriving_planes
  20. GROUP BY runway_sign
  21. ) UNION ALL
  22. (SELECT runway_sign as Landebahn, 0 as [Anzahl gelandet], COUNT(runway_sign) as [Anzahl gestartet]
  23. FROM tbl_leaving_planes
  24. GROUP BY runway_sign
  25. )
  26. ) al
  27. GROUP BY runway_sign;
  28.  
  29. SELECT COALESCE(a.runway_sign, l.runway_sign) as Landebahn,
  30. a.[Anzahl gelandet],
  31. l.[Anzahl gestartet]
  32. FROM (SELECT runway_sign, COUNT(runway_sign) as [Anzahl gelandet]
  33. FROM tbl_arriving_planes
  34. GROUP BY runway_sign
  35. ) a FULL JOIN
  36. (SELECT runway_sign as Landebahn, COUNT(runway_sign) as [Anzahl gestartet]
  37. FROM tbl_leaving_planes
  38. GROUP BY runway_sign
  39. ) l
  40. ON l.runway_sign = a.runway_sign
  41.  
  42. SELECT runway_sign as Landebahn, SUM([Anzahl gelandet]) as [Anzahl gelandet], SUM([Anzahl gestartet]) as [Anzahl gestartet]
  43. FROM (
  44. SELECT runway_sign, 1 as [Anzahl gelandet], 0 as [Anzahl gestartet] FROM tbl_arriving_planes
  45. UNION
  46. SELECT runway_sign, 0 as [Anzahl gelandet], 1 as [Anzahl gestartet] FROM tbl_leaving_planes
  47. ) as a
  48. GROUP BY runway_sign
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement