How to display records not older than one month ago in SQL Oracle?-CodePudding

I want to display results only this month? How can I do it with oracle SQL?

select 'Working Equipment' label, count(*)
from LOGS_HISTORY
where MODE__ = 'Working'
group by MODE__ 
having TIME_ < add_months(sysdate, -1)
union all
select 'Standby Equipment' label, count(distinct TAG_IDENTIFIER)
from LOGS_HISTORY
where MODE__ = 'Standby'

Please see my code snippet and advise on how can i solve it? Currently I have error because there is no group by in my code

CodePudding user response：

Apply the time filter in the WHERE clause rather than the HAVING clause. If you want it since exactly 1 month before the current instant (including time) then:

SELECT 'Working Equipment' label,
       count(*)
FROM   LOGS_HISTORY
WHERE  MODE__ = 'Working'
AND    TIME_ >= add_months(sysdate, -1)
union all
SELECT 'Standby Equipment' label,
       count(distinct TAG_IDENTIFIER)
FROM   LOGS_HISTORY
WHERE  MODE__ = 'Standby'
AND    TIME_ >= add_months(sysdate, -1)

If you want it in the current month then:

SELECT 'Working Equipment' label,
       count(*)
FROM   LOGS_HISTORY
WHERE  MODE__ = 'Working'
AND    TIME_ >= TRUNC(sysdate, 'MM')
union all
SELECT 'Standby Equipment' label,
       count(distinct TAG_IDENTIFIER)
FROM   LOGS_HISTORY
WHERE  MODE__ = 'Standby'
AND    TIME_ >= TRUNC(sysdate, 'MM')