Advertisement
Guest User

Untitled

a guest
Jun 18th, 2019
75
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.92 KB | None | 0 0
  1. SELECT * FROM
  2. (
  3. SELECT to_char(co.created_at, 'YYYY-MM') AS month
  4. , COUNT(co.id) AS co_count
  5. , COUNT(tm.id) AS tm_count
  6. FROM classroom_observations co
  7. LEFT JOIN training_modules tm
  8. ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
  9. GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM')
  10. UNION
  11. SELECT to_char(tm.created_at, 'YYYY-MM') AS month
  12. , COUNT(co.id) AS co_count
  13. , COUNT(tm.id) AS tm_count
  14. FROM classroom_observations co
  15. RIGHT JOIN training_modules tm
  16. ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
  17. GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM')
  18. ) tbl
  19. ORDER BY Month;
  20.  
  21. month | co_count | tm_count
  22. ---------+----------+----------
  23. 2019-03 | 0 | 3
  24. 2019-04 | 1 | 0
  25. 2019-05 | 6 | 6
  26.  
  27. SELECT * FROM
  28. (
  29. SELECT to_char(co.created_at, 'YYYY-MM') AS month
  30. , COUNT(co.id) AS co_count
  31. , COUNT(tm.id) AS tm_count
  32. , COUNT(tw.id) AS tw_count
  33. FROM classroom_observations co
  34. LEFT JOIN training_modules tm
  35. ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
  36. LEFT JOIN teachers_workshops tw
  37. ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
  38. GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM') , to_char(tw.created_at, 'YYYY-MM')
  39. UNION
  40. SELECT to_char(tm.created_at, 'YYYY-MM') AS month
  41. , COUNT(co.id) AS co_count
  42. , COUNT(tm.id) AS tm_count
  43. , COUNT(tw.id) AS tw_count
  44. FROM classroom_observations co
  45. RIGHT JOIN training_modules tm
  46. ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
  47. RIGHT JOIN teachers_workshops tw
  48. ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
  49. GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM'), to_char(tw.created_at, 'YYYY-MM')
  50. UNION
  51. SELECT to_char(tm.created_at, 'YYYY-MM') AS month
  52. , COUNT(co.id) AS co_count
  53. , COUNT(tm.id) AS tm_count
  54. , COUNT(tw.id) AS tw_count
  55. FROM classroom_observations co
  56. RIGHT JOIN training_modules tm
  57. ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
  58. RIGHT JOIN teachers_workshops tw
  59. ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
  60. GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM'), to_char(tw.created_at, 'YYYY-MM')
  61. ) tbl
  62. ORDER BY Month;
  63.  
  64. month | co_count | tm_count | tw_count
  65. ---------+----------+----------+----------
  66. 2019-04 | 1 | 0 | 0
  67. 2019-05 | 6 | 6 | 6
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement