Postgresql SELECT and count by date

Question

I have a postgresql table called login_logs which has the following fields:

date, time, username, ip_addr

I have the following SQL that gets logins greater than or equal to a certain date:

SELECT * FROM login_logs WHERE date>='2016-10-01'

However, I want to only get list of usernames and dates WHERE a username has more than one login on the same day. So for example if a username has an entry twice or more then it should return that username, the date with the count of how many logins for that day. So if the user has another date with more than two logins too that should also show up with the username, date and the count.

Any ideas how I can do this?

date is a bad name for a column. So is time. BTW: it is more convenient to combine date+time into a timestamp, and use that. — wildplasser
– wildplasser, Commented Feb 4, 2017 at 15:18

Wander Nauta · Accepted Answer · 2017-02-04 15:22:48Z

9

Something like this would do the trick:

SELECT
  username,
  COUNT(*) as count,
  date_trunc('day', date) as day
FROM login_logs
WHERE date>='2016-10-01'
GROUP BY username, day 
HAVING COUNT(*) > 1

We group logins both by username and by day, because we want to be reasoning about groups of logins by the same user on the same day. Then, we only keep the groups that have more than one entry.

answered Feb 4, 2017 at 15:22

Wander Nauta

19.7k1 gold badge50 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

McNets · Accepted Answer · 2017-02-04 15:34:44Z

0

In Postgres you can use COUNT(DISTINC)

SELECT username, ip_addr, count(distinct date) as count_dates
FROM login_logs
GROUP BY username, ip_addr
WHERE date >= '2016-10-01';

answered Feb 4, 2017 at 15:34

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Collectives™ on Stack Overflow

Postgresql SELECT and count by date

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related