Select average for several periods from database

Question

I need to get average count for same period in several time intervals.
So now my query looks pretty ugly, is it possible to make it better? For example if I want to get data for 7 week I Need copy/paste pretty same lines 7 times, argh!
Also I have a question about how to discard extreme values (0, 10,15, 11 - remove 0 from the samples).

SELECT
  ROUND((w1 + w2 + w3 + w4) / 4)
FROM (
  SELECT COUNT(*) AS w1
  FROM a.b
  WHERE id = 1 AND DATE BETWEEN sysdate - 7 - (1 / 24 / 4) AND sysdate - 7),
  (SELECT COUNT(*) AS w2
   FROM a.b
   WHERE
     id = 1 AND DATE BETWEEN sysdate - 14 - (1 / 24 / 4) AND sysdate - 14),
  (SELECT COUNT(*) as w3
FROM a.b
WHERE id = 1 AND DATE BETWEEN sysdate - 21 - (1 / 24 / 4) AND sysdate - 21),
  (SELECT COUNT(*) as w4
FROM a.b
WHERE id = 1 AND DATE BETWEEN sysdate - 28 - (1 / 24 / 4) AND sysdate - 28);

If it's important, I'm using Oracle DB.

Answer 1

Maybe try using a GROUP BY.

SELECT AVG(C) FROM (
SELECT SUM(1) AS C
,to_number(to_char(date,'WW')) as DT FROM a.b
GROUP BY to_number(to_char(date,'WW')))