select	extract(hour from e.dt_passagem) as hora,
	extract(day from e.dt_passagem) as dia,
	infracoes.qtde as infracao,
	count(e.id_estatistica) as volume,
	avg(e.vel_medida) as velocidademedia

from    estatistica e
left join (
		select	extract(hour from r.dt_registro) as hora,
			extract(day from r.dt_registro) as dia,
			count(*) as qtde
		from    registro r
		where   r.id_tipo_infracao = 1 and
			(r.dt_registro between '2009-10-01 00:00:00' and '2009-10-31 23:59:59') and
			r.id_faixa = 46
		group by extract(hour from r.dt_registro),extract(day from r.dt_registro)
		order by extract(day from r.dt_registro),extract(hour from r.dt_registro)
	) as infracoes
on	infracoes.dia = extract(day from e.dt_passagem) and
	infracoes.hora = extract(hour from e.dt_passagem)

where   (e.dt_passagem between '2009-10-01 00:00:00' and '2009-10-31 23:59:59') and
	e.id_faixa = 46

group by extract(hour from e.dt_passagem),infracoes.qtde ,extract(day from e.dt_passagem)
order by extract(day from e.dt_passagem)