Reputation: 9827
I'm using Oracle 12c and I need to get the count of unique username rows for each day. Currently the table below has multiple rows with the same username on the same day (date_created).
LOGIN_HISTORY
----------------
id (unique random generated long)
date_created (timestamp)
username (var char)
Upvotes: 1
Views: 72
Reputation: 522817
In the query below I group using the day, month, and year, which is required to uniquely identify each day your date_created
column. I used COUNT(DISTINCT username)
to identify the count of unique usernames for a given day.
SELECT TO_CHAR(date_created, 'DD') DAY,
TO_CHAR(date_created, 'MM') MONTH,
TO_CHAR(date_created, 'YYYY') YEAR,
COUNT(DISTINCT username) AS userCount
FROM LOGIN_HISTORY
GROUP BY TO_CHAR(date_created, 'DD'),
TO_CHAR(date_created, 'MM'),
TO_CHAR(date_created, 'YYYY')
Upvotes: 1