-- Put together manually.
-- Could not see rep for all polls
-- Replies counted in different ways. Should really be double checked.
-- ANSI date format used to aid working in another RDBMS
-- 12/31 counted in following year.
WITH
	Poll(Posted, Votes, Replies, Rep, Id)
AS
	(
	 SELECT DATE '2013-12-31', 0367, 014, NULL, 1068839 FROM Dual UNION ALL
	 SELECT DATE '2014-02-01', 0471, 014, NULL, 1072962 FROM Dual UNION ALL
	 SELECT DATE '2014-03-01', 0617, 012, 15, 1076665 FROM Dual UNION ALL
	 SELECT DATE '2014-04-01', 0685, 010, 21, 1080513 FROM Dual UNION ALL
	 SELECT DATE '2014-05-01', 0790, 052, 13, 1084594 FROM Dual UNION ALL
	 SELECT DATE '2014-06-01', 0641, 027, 18, 1088179 FROM Dual UNION ALL
	 SELECT DATE '2014-07-01', 0304, 023, 13, 1091827 FROM Dual UNION ALL
	 SELECT DATE '2014-07-16', 0261, 039, 15, 1093839 FROM Dual UNION ALL
	 SELECT DATE '2014-08-01', 0381, 024, 19, 1095870 FROM Dual UNION ALL
	 SELECT DATE '2014-08-16', 0300, 038, 19, 1097658 FROM Dual UNION ALL
	 SELECT DATE '2014-09-01', 0158, 026, 05, 1099153 FROM Dual UNION ALL
	 SELECT DATE '2014-09-16', 0395, 026, 16, 1100721 FROM Dual UNION ALL
	 SELECT DATE '2014-10-01', 0385, 032, 16, 1102521 FROM Dual UNION ALL
	 SELECT DATE '2014-10-16', 0230, 032, 12, 1104030 FROM Dual UNION ALL
	 SELECT DATE '2014-11-01', 0260, 035, 21, 1105789 FROM Dual UNION ALL
	 SELECT DATE '2014-11-17', 0214, 012, 19, 1107394 FROM Dual UNION ALL
	 SELECT DATE '2014-12-01', 0406, 014, 14, 1108806 FROM Dual UNION ALL
	 SELECT DATE '2014-12-17', 0244, 018, 16, 1110616 FROM Dual UNION ALL
	 SELECT DATE '2015-01-01', 0306, 013, 13, 1111889 FROM Dual UNION ALL
	 SELECT DATE '2015-01-16', 0263, 023, 25, 1113517 FROM Dual UNION ALL
	 SELECT DATE '2015-02-01', 0472, 025, 21, 1115220 FROM Dual UNION ALL
	 SELECT DATE '2015-03-01', 0681, 020, 15, 1118273 FROM Dual UNION ALL
	 SELECT DATE '2015-04-01', 0579, 028, 14, 1122103 FROM Dual UNION ALL
	 SELECT DATE '2015-05-01', 0610, 018, 10, 1125345 FROM Dual UNION ALL
	 SELECT DATE '2015-06-01', 0406, 028, 11, 1128535 FROM Dual UNION ALL
	 SELECT DATE '2003-12-31', 0381, 038, NULL,  317976 FROM Dual UNION ALL
	 SELECT DATE '2004-01-10', 0554, 073, NULL,  320300 FROM Dual UNION ALL
	 SELECT DATE '2004-01-31', 0493, 043, NULL,  325590 FROM Dual UNION ALL
	 SELECT DATE '2004-02-13', 0231, 040, NULL,  328924 FROM Dual UNION ALL
	 SELECT DATE '2004-02-20', 0528, 038, NULL,  330663 FROM Dual UNION ALL
	 SELECT DATE '2004-03-14', 0674, 035, NULL,  336465 FROM Dual UNION ALL
	 SELECT DATE '2004-03-28', 0447, 025, NULL,  340356 FROM Dual UNION ALL
	 SELECT DATE '2004-04-06', 0685, 035, NULL,  343106 FROM Dual UNION ALL
	 SELECT DATE '2004-04-27', 0354, 047, NULL,  348710 FROM Dual UNION ALL
	 SELECT DATE '2004-05-06', 0239, 016, NULL,  351331 FROM Dual UNION ALL
	 SELECT DATE '2004-05-09', 1134, 055, NULL,  351805 FROM Dual UNION ALL
	 SELECT DATE '2004-05-27', 0750, 092, NULL,  357125 FROM Dual UNION ALL
	 SELECT DATE '2004-06-16', 0676, 032, NULL,  367278 FROM Dual UNION ALL
	 SELECT DATE '2004-07-14', 0847, 096, NULL,  374488 FROM Dual UNION ALL
	 SELECT DATE '2004-08-02', 0331, 040, NULL,  379195 FROM Dual UNION ALL
	 SELECT DATE '2004-08-16', 0721, 048, NULL,  383208 FROM Dual UNION ALL
	 SELECT DATE '2004-08-31', 1208, 097, NULL,  387377 FROM Dual UNION ALL
	 SELECT DATE '2004-09-27', 0627, 067, NULL,  394350 FROM Dual UNION ALL
	 SELECT DATE '2004-10-19', 0570, 047, NULL,  400428 FROM Dual UNION ALL
	 SELECT DATE '2004-11-02', 0384, 047, NULL,  404708 FROM Dual UNION ALL
	 SELECT DATE '2004-11-08', 0719, 039, NULL,  405984 FROM Dual UNION ALL
	 SELECT DATE '2004-11-17', 0603, 031, NULL,  408565 FROM Dual UNION ALL
	 SELECT DATE '2004-12-15', 0385, 025, NULL,  415282 FROM Dual UNION ALL
	 SELECT DATE '2004-12-31', 0516, 037, NULL,  418471 FROM Dual UNION ALL
	 SELECT DATE '2005-01-15', 0975, 034, NULL,  422479 FROM Dual UNION ALL
	 SELECT DATE '2005-02-15', 0941, 076, NULL,  431048 FROM Dual UNION ALL
	 SELECT DATE '2005-03-16', 0702, 088, NULL,  439956 FROM Dual UNION ALL
	 SELECT DATE '2005-04-09', 0359, 022, NULL,  446224 FROM Dual UNION ALL
	 SELECT DATE '2005-04-23', 0304, 019, NULL,  450653 FROM Dual UNION ALL
	 SELECT DATE '2005-05-05', 0526, 051, NULL,  454408 FROM Dual UNION ALL
	 SELECT DATE '2005-05-27', 0383, 069, NULL,  461272 FROM Dual UNION ALL
	 SELECT DATE '2005-06-08', 0919, 044, NULL,  464548 FROM Dual UNION ALL
	 SELECT DATE '2005-07-12', 0462, 029, NULL,  474189 FROM Dual UNION ALL
	 SELECT DATE '2005-07-21', 0152, 027, NULL,  476741 FROM Dual UNION ALL
	 SELECT DATE '2005-07-27', 0275, 021, NULL,  478760 FROM Dual UNION ALL
	 SELECT DATE '2005-08-04', 0293, 028, NULL,  480707 FROM Dual UNION ALL
	 SELECT DATE '2005-08-10', 0288, 035, NULL,  482550 FROM Dual UNION ALL
	 SELECT DATE '2005-08-17', 0249, 044, NULL,  484367 FROM Dual UNION ALL
	 SELECT DATE '2005-08-24', 0194, 004, NULL,  486258 FROM Dual UNION ALL
	 SELECT DATE '2005-09-06', 0141, 048, NULL,  489623 FROM Dual UNION ALL
	 SELECT DATE '2005-09-08', 0294, 056, NULL,  490121 FROM Dual UNION ALL
	 SELECT DATE '2005-09-13', 0427, 048, NULL,  491469 FROM Dual UNION ALL
	 SELECT DATE '2005-09-22', 0270, 023, NULL,  494181 FROM Dual UNION ALL
	 SELECT DATE '2005-09-29', 0478, 034, NULL,  496105 FROM Dual UNION ALL
	 SELECT DATE '2005-10-09', 0248, 028, NULL,  498505 FROM Dual UNION ALL
	 SELECT DATE '2005-10-12', 0560, 029, NULL,  499711 FROM Dual UNION ALL
	 SELECT DATE '2005-10-30', 0122, 031, NULL,  504009 FROM Dual UNION ALL
	 SELECT DATE '2005-11-01', 0096, 011, NULL,  504562 FROM Dual UNION ALL
	 SELECT DATE '2005-11-02', 0310, 022, NULL,  504834 FROM Dual UNION ALL
	 SELECT DATE '2005-11-08', 0205, 032, NULL,  506825 FROM Dual UNION ALL
	 SELECT DATE '2005-11-16', 0646, 106, NULL,  509130 FROM Dual UNION ALL
	 SELECT DATE '2005-12-06', 0358, 052, NULL,  514544 FROM Dual UNION ALL
	 SELECT DATE '2005-12-14', 0291, 024, NULL,  516577 FROM Dual UNION ALL
	 SELECT DATE '2005-12-20', 0263, 031, NULL,  518012 FROM Dual UNION ALL
	 SELECT DATE '2005-12-26', 0293, 031, NULL,  519073 FROM Dual
	),
	Split
AS
	(
	 SELECT
		Posted,
		TO_CHAR(Posted, 'YY')
		+ CASE
		 WHEN TO_CHAR(Posted, 'MM') = 12 AND TO_CHAR(Posted, 'DD') = 31  THEN 1
		 ELSE 0
		END Y, 
		CASE
		 WHEN TO_CHAR(Posted, 'MM') = 12 AND TO_CHAR(Posted, 'DD') = 31  THEN '01'
		 ELSE TO_CHAR(Posted, 'MM')
		END M,
		Votes,
		Replies,
		Rep,
		Id
	 FROM
		Poll
	)
SELECT
	Y,
	M,
	ROUND(AVG(Votes)) Votes,
	ROUND(AVG(Replies)) Replies
FROM
	Split
GROUP BY
	ROLLUP(Y, M)
ORDER BY
	Y,
	M;
##</code><code>##

--same CTEs, different main query
SELECT
	Say.M,
	ROUND(AVG(Say.Votes)) Votes,
	ROUND(AVG(CASE Split.Y WHEN 04 THEN  Split.Votes END)) V_04,
	ROUND(AVG(CASE Split.Y WHEN 05 THEN  Split.Votes END)) V_05,
	ROUND(AVG(CASE Split.Y WHEN 14 THEN  Split.Votes END)) V_14,
	ROUND(AVG(CASE Split.Y WHEN 15 THEN  Split.Votes END)) V_15,
	ROUND(AVG(Say.Replies)) Replies,
	ROUND(AVG(CASE Split.Y WHEN 04 THEN  Split.Replies END)) R_04,
	ROUND(AVG(CASE Split.Y WHEN 05 THEN  Split.Replies END)) R_05,
	ROUND(AVG(CASE Split.Y WHEN 14 THEN  Split.Replies END)) R_14,
	ROUND(AVG(CASE Split.Y WHEN 15 THEN  Split.Replies END)) R_15
FROM
	Split,
	Split Say --Split All Years
WHERE
	Say.M = Split.M
GROUP BY
	Say.M
ORDER BY
	Say.M;

##</code><code>##

SELECT
	Y,
	M,
	COUNT(*) Total
FROM
	Split
GROUP BY
	ROLLUP(Y, M)
ORDER BY
	Y,
	M;