Get data of last Month day by day in oracle sql-CodePudding

I want to get data from last month day by day, I can get the last 30 days but I just want the month as it may be less or more than 30 days,

this is the query for getting the last 30 days

SELECT trunc(timestamp) , count(*) FROM table1 where trunc(timestamp) > trunc(sysdate -30) group by trunc(timestamp) order by 1;

Also, I am using it in a shell script if I can make a variable in the script and put it the query

CodePudding user response：

To get data from the start of the current month until today:

SELECT TRUNC(timestamp) AS day,
       COUNT(*)
FROM   table1
WHERE  timestamp >= TRUNC(SYSDATE, 'MM')
AND    timestamp <  TRUNC(SYSDATE)   INTERVAL '1' DAY
GROUP BY TRUNC(timestamp)
ORDER BY day

To get data from the same day last month until today:

SELECT TRUNC(timestamp) AS day,
       COUNT(*)
FROM   table1
WHERE  timestamp >= ADD_MONTHS(TRUNC(SYSDATE), -1)
AND    timestamp <  TRUNC(SYSDATE)   INTERVAL '1' DAY
GROUP BY TRUNC(timestamp)
ORDER BY day

db<>fiddle here