ORACLE SQL: Group the data by the last 4 weeks

i have a trouble with dates, i need to do a query that count the ids from the last four weeks.

I tried this, but it doesn’t works.

SELECT count(a.id), sysdate
FROM table_1 a, table_2 b
WHERE b.fk_id = a.id
AND a.column = some_id
CONNECT BY LEVEL <=4

I need a output like this

| count(a.id) | week |
|      2      |   1  |
|      6      |   2  |
|      7      |   3  |
|      21     |   4  |

So, the ” count(a.id) ” values are the count of the ID’s in one of the past 4 weeks.

Answer

Here’s a MS SQL Server solution. You should be able to convert it to Oracle if needed.

select count(id) as 'count(a.id)'
, datepart(week, MyDate) as 'week'

from table_1

where datepart(week, MyDate) between datepart(week, getdate()) - 5 and datepart(week, getdate()) - 1

group by datepart(week, MyDate)

And here’s my attempt at doing this in Oracle.

select count(a.id)
, Week

from (
  select cast(TO_CHAR(MyDate, 'WW') as int) + 
    case when cast(TO_CHAR(MyDate, 'D') as int) < cast(TO_CHAR(trunc(MyDate, 'year'), 'D') as int) then 1 else 0 end  Week
  , id
  , MyDate

  from table_1
  ) a

where a.MyDate between sysdate - cast(TO_CHAR(sysdate, 'D') as int) - 28 + 1 and sysdate - cast(TO_CHAR(sysdate, 'D') as int)

group by Week