CODEWITHSUNDEEP
oracle-databasedatecount
Kevin B.
Kevin B.

Reputation: 13

Oracle, count of distinct items by date

I need to get a count of items by date from field1 grouped by last update time. What I am looking for is how many times an item from field1 appears on a specific date for the last 30 days where field 2 = 0. This will be run every day so the date will roll. Field1 will be a number >0, field2 will be any number (negative and positive), last_upd_time will be a system time when the last update occurred. I don't need the time, only the date. My current query that returns all of the data is:

select field1, field2, trunc(last_upd_time)
from table 
where field2 = '0' and last_upd_time >= SYSDATE - 30

I have attempted to use count, group by, and group by/having. Not saying I was using them correctly, but I did try.

Upvotes: 1

Views: 134

Answers (1)

cdaiga
cdaiga

Reputation: 4939

Try this:

SELECT TO_CHAR(last_upd_time,'DD-MM-YYYY') last_upd_time, COUNT(DISTINCT field1)
FROM table WHERE field2='0' AND
last_upd_time>=SYSDATE - INTERVAL '30' DAY
GROUP BY TO_CHAR(last_upd_time,'DD-MM-YYYY');

Upvotes: 1

Related Questions