CODEWITHSUNDEEP
oracle-database
c12
c12

Reputation: 9827

Get Distinct Column Count By Day

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

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

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

Related Questions