--all times - all schools

select 
	
	--distinct tablewithcounts.project_name
	tablewithcounts.freq_val, count(tablewithcounts.id)
	

--tablewithcounts.id, tablewithcounts.project_name, tablewithcounts.metric_name, tablewithcounts."time", tablewithcounts."Day", tablewithcounts."Hour", tablewithcounts.value, tablewithcounts.freq_val, tablewithcounts.display_value --, geometry_id

from
(
SELECT
	id, project_name, metric_name, "time", value, display_value, geometry_id,
	round(cast(value as numeric), 1) as freq_val,
	extract(hour from "time") as "Hour",
	to_char("time", 'Day') as "Day"
	FROM public.metrics_app_timeandgeometric_historic
	--where project_name in ('Lasswade')
	where 
	(
	project_name = 'Lasswade'
	or project_name = 'Mauricewood_School'
	or project_name = 'Eaglesham_Primaryschool'
	or
	project_name in (
			'Lasswade'
			--'Roman_Baths',
			'Mauricewood_School',
			--'Leconfield_House',
			'Inverkeithing_Nursery',
			'Windmill',
			'Dunfermline_Highschool',
			'Kinross_Primaryschool',
			'Pitlochry_Highschool',
			'Tulloch_Primaryschool',
			'Viewlands_Primaryschool',
			'Busby_Nursery',
			'Carlibar_Primaryschool',
			'Eaglesham_Elc',
			'Eaglesham_Primaryschool',
			'Glen_Family_Centre',
			'Mearns_Castle_Highschool',
			'Williamwood_Highschool',
			'Glenwood_Family_Centre',
			'Isobel_Mair',
			'Maidenhill_Primaryschool',
			'Olm_Primaryschool',
			'Thornliebank_Primaryschool',
			'St_Lukes_Primaryschool',
			'Abroath_Academy',
			'Abroath_Highschool',
			'Eassie_Primaryschool',
			'Maisondue_Primaryschool',
			'Northmuir_Primaryschool',
			'St_Margarets_Primaryschool',
			'Strathmartine_Primaryschool',
			'Tealing_Primaryschool',
			'Websters_Highschool'
			)
	)
	and metric_name = 'temp'
	and (value >= 0 and value < 50)
	and display_value is NULL
) as tablewithcounts	


-- where 
-- --tablewithcounts."Day" not in ('Saturday ', 'Sunday   ')
-- tablewithcounts."Day" not like '%Saturday%'
-- and tablewithcounts."Day" not like '%Sunday%'
-- and tablewithcounts."Hour" >= 8 and tablewithcounts."Hour" < 16

-- and tablewithcounts.time not between '2021-08-01' and '2021-08-17'
-- and tablewithcounts.time not between '2021-09-20' and '2021-09-20'
-- and tablewithcounts.time not between '2021-10-18' and '2021-10-25'
-- and tablewithcounts.time not between '2021-12-23' and '2022-01-10'
-- and tablewithcounts.time not between '2022-02-14' and '2022-02-18'
-- and tablewithcounts.time not between '2022-04-11' and '2022-04-22'
-- and tablewithcounts.time not between '2022-05-02' and '2022-05-02'
-- and tablewithcounts.time not between '2022-05-23' and '2022-05-23'
-- and tablewithcounts.time not between '2022-07-01' and '2022-07-31'
-- and tablewithcounts.time not between '2022-08-01' and '2022-08-16'
-- and tablewithcounts.time not between '2022-09-16' and '2022-09-19'
-- and tablewithcounts.time not between '2022-10-17' and '2022-10-24'
-- and tablewithcounts.time not between '2022-12-21' and '2023-01-04'
-- and tablewithcounts.time not between '2023-02-13' and '2023-02-17'
-- and tablewithcounts.time not between '2023-04-03' and '2023-04-14'
-- and tablewithcounts.time not between '2023-05-01' and '2023-05-01'
-- and tablewithcounts.time not between '2023-05-22' and '2023-05-22'
-- and tablewithcounts.time not between '2023-06-29' and '2023-07-31'


group by tablewithcounts.freq_val
order by tablewithcounts.freq_val

--limit 1000