/*

	Print a cross tabulation (Excel pivot table)
	showing 
		the total number of in-spill events
		from good runs
		for each target
		and each momentum

	The momenta have been summed over both signs.
	and only high-statistics values are shown.

	You can get the list of all momenta and total
	events from
*/
\echo All momenta, in 5 GeV bins:
SELECT
	'Bin' as "Type",
	round(abs(momentum)/5) * 5 as "Bin",
	momentum as "Momentum",
	round(sum(triginspill)/1000000.,2) as "Total MEvents"
FROM
	runs natural join trigstat
WHERE
	goodrun = 1
GROUP BY
	"Bin",
	"Momentum"
UNION
SELECT
	'Runs',
	count(abs(momentum)),
	count(momentum),
	round(sum(triginspill)/1000000.,2) as "Total MEvents"
FROM
	runs natural join trigstat
WHERE
	goodrun = 1
ORDER BY
	"Bin",
	"Momentum"
;


-- Main table, events in M
\echo All production events, in millions, by target and momentum bin:
SELECT 
	target as "Target",

	round(sum(case when (round(abs(momentum)/5) * 5)=0
		then spillevt_total else 0 end)/1000000.,2) as "  0 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=5
		then spillevt_total else 0 end)/1000000.,2) as "  5 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=10
		then spillevt_total else 0 end)/1000000.,2) as " 10 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=20
		then spillevt_total else 0 end)/1000000.,2) as " 20 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=35
		then spillevt_total else 0 end)/1000000.,2) as " 35 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=40
		then spillevt_total else 0 end)/1000000.,2) as " 40 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=45
		then spillevt_total else 0 end)/1000000.,2) as " 45 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=55
		then spillevt_total else 0 end)/1000000.,2) as " 55 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=60
		then spillevt_total else 0 end)/1000000.,2) as " 60 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=65
		then spillevt_total else 0 end)/1000000.,2) as " 65 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=85
		then spillevt_total else 0 end)/1000000.,2) as " 85 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=120
		then spillevt_total else 0 end)/1000000.,2) as "120 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=1000
		then spillevt_total else 0 end)/1000000.,2) as " 1000 GeV",

	round(sum(spillevt_total)/1000000.,2) as "  Total"

FROM
	stats_summary

GROUP BY
	"Target"


-- Momentum and Grand Total, in K
UNION
SELECT
	'zGrand Total' as "Target",

	round(sum(case when (round(abs(momentum)/5) * 5)=0
		then spillevt_total else 0 end)/1000000.,2) as "  0 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=5
		then spillevt_total else 0 end)/1000000.,2) as "  5 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=10
		then spillevt_total else 0 end)/1000000.,2) as " 10 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=20
		then spillevt_total else 0 end)/1000000.,2) as " 20 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=35
		then spillevt_total else 0 end)/1000000.,2) as " 35 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=40
		then spillevt_total else 0 end)/1000000.,2) as " 40 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=45
		then spillevt_total else 0 end)/1000000.,2) as " 45 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=55
		then spillevt_total else 0 end)/1000000.,2) as " 55 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=60
		then spillevt_total else 0 end)/1000000.,2) as " 60 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=65
		then spillevt_total else 0 end)/1000000.,2) as " 65 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=85
		then spillevt_total else 0 end)/1000000.,2) as " 85 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=120
		then spillevt_total else 0 end)/1000000.,2) as "120 GeV",
	round(sum(case when (round(abs(momentum)/5) * 5)=1000
		then spillevt_total else 0 end)/1000000.,2) as " 1000 GeV",

	round(
		sum(
			case when
				(	(round(abs(momentum)/5) * 5) =  0
				OR	(round(abs(momentum)/5) * 5) =  5
				OR	(round(abs(momentum)/5) * 5) = 10
				OR	(round(abs(momentum)/5) * 5) = 20
				OR	(round(abs(momentum)/5) * 5) = 35
				OR	(round(abs(momentum)/5) * 5) = 40
				OR	(round(abs(momentum)/5) * 5) = 45
				OR	(round(abs(momentum)/5) * 5) = 55
				OR	(round(abs(momentum)/5) * 5) = 60
				OR	(round(abs(momentum)/5) * 5) = 65
				OR	(round(abs(momentum)/5) * 5) = 85
				OR	(round(abs(momentum)/5) * 5) = 120
				OR	(round(abs(momentum)/5) * 5) = 1000
				)
			then spillevt_total
			else 0
			end
			)
		/1000000.,2) as "  Total"

FROM
	stats_summary

WHERE
		(round(abs(momentum)/5) * 5) =  0
	OR	(round(abs(momentum)/5) * 5) =  5
	OR	(round(abs(momentum)/5) * 5) = 10
	OR	(round(abs(momentum)/5) * 5) = 20
	OR	(round(abs(momentum)/5) * 5) = 35
	OR	(round(abs(momentum)/5) * 5) = 40
	OR	(round(abs(momentum)/5) * 5) = 45
	OR	(round(abs(momentum)/5) * 5) = 55
	OR	(round(abs(momentum)/5) * 5) = 60
	OR	(round(abs(momentum)/5) * 5) = 65
	OR	(round(abs(momentum)/5) * 5) = 85
	OR	(round(abs(momentum)/5) * 5) = 120
	OR	(round(abs(momentum)/5) * 5) = 1000

ORDER BY
	"Target"
;
