Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM
- (
- SELECT to_char(co.created_at, 'YYYY-MM') AS month
- , COUNT(co.id) AS co_count
- , COUNT(tm.id) AS tm_count
- FROM classroom_observations co
- LEFT JOIN training_modules tm
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
- GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM')
- UNION
- SELECT to_char(tm.created_at, 'YYYY-MM') AS month
- , COUNT(co.id) AS co_count
- , COUNT(tm.id) AS tm_count
- FROM classroom_observations co
- RIGHT JOIN training_modules tm
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
- GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM')
- ) tbl
- ORDER BY Month;
- month | co_count | tm_count
- ---------+----------+----------
- 2019-03 | 0 | 3
- 2019-04 | 1 | 0
- 2019-05 | 6 | 6
- SELECT * FROM
- (
- SELECT to_char(co.created_at, 'YYYY-MM') AS month
- , COUNT(co.id) AS co_count
- , COUNT(tm.id) AS tm_count
- , COUNT(tw.id) AS tw_count
- FROM classroom_observations co
- LEFT JOIN training_modules tm
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
- LEFT JOIN teachers_workshops tw
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
- GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM') , to_char(tw.created_at, 'YYYY-MM')
- UNION
- SELECT to_char(tm.created_at, 'YYYY-MM') AS month
- , COUNT(co.id) AS co_count
- , COUNT(tm.id) AS tm_count
- , COUNT(tw.id) AS tw_count
- FROM classroom_observations co
- RIGHT JOIN training_modules tm
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
- RIGHT JOIN teachers_workshops tw
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
- GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM'), to_char(tw.created_at, 'YYYY-MM')
- UNION
- SELECT to_char(tm.created_at, 'YYYY-MM') AS month
- , COUNT(co.id) AS co_count
- , COUNT(tm.id) AS tm_count
- , COUNT(tw.id) AS tw_count
- FROM classroom_observations co
- RIGHT JOIN training_modules tm
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tm.created_at, 'YYYY-MM')
- RIGHT JOIN teachers_workshops tw
- ON to_char(co.created_at, 'YYYY-MM') = to_char(tw.created_at, 'YYYY-MM')
- GROUP BY to_char(co.created_at, 'YYYY-MM'), to_char(tm.created_at, 'YYYY-MM'), to_char(tw.created_at, 'YYYY-MM')
- ) tbl
- ORDER BY Month;
- month | co_count | tm_count | tw_count
- ---------+----------+----------+----------
- 2019-04 | 1 | 0 | 0
- 2019-05 | 6 | 6 | 6
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement