CODEWITHSUNDEEP
mysqlsqlselect
Desiderantes
Desiderantes

Reputation: 159

SQL - Selecting various counts

I have the following tables:

User
-------------------
id BIGINT
name text

Session
-------------------
id BIGINT
username text
last_login datetime

And want to get the total user count, AND the count of users which have a session with a last login from today. How can i model the query?

Upvotes: 0

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Let me assume that you fix the session table to have userId rather than userName. Then your answer depends on the database. The general idea is something like this:

select count(distinct u.id) as user_count,  -- including those with no sessions
       sum(case when cast(last_login as date) = CURRENT_DATE
from users u left join
     sessions s
     on u.id = s.userId ;

This is the sketch of the logic. Removing the time component from a date/time values varies by database. The way of representing the current date varies by database.

Upvotes: 1

Related Questions