Conditional Counting

Question

I'm analyzing a database of login records for a university and I'm trying to write a query that will give me an idea as to how many users are using the labs for short bursts, verse a more extended stay.

I have the query below query, which tells me how many times each user has used a lab for less than or equal to 10 minutes. What I want is a result that tells me this and their total number of logins.

select username, count(*) as "<= 10 min"
    from logins
    where duration<="600"
    group by username
    order by username asc
    limit 10;

Example desired output:

+----------+----------+----------+
| username | <=10 Min |  total   |
+----------+----------+----------+
| user1    |        4 |        7 |
| user2    |       11 |       22 |
| user3    |        1 |        3 |
| user4    |        4 |        8 |
+----------+----------+----------+

Answer 1

If you want to return both the total number of logins and the total number of sessions that are less than 10 minutes, then you can get the result using your current query with some minor changes.

You can move the where clause filter inside of a CASE expression to get the count of the sessions less than 10 minutes. Then just add a second count() to get the total number of sessions for each username.

select username, 
  count(case when duration<=600 then username end) as `<= 10 min`,
  count(username) TotalLogins
from logins
group by username
order by username asc
limit 10;

See SQL Fiddle with Demo. You could also use the MySQL shorthand IF() syntax instead of the CASE expression:

select username, 
  count(if(duration<=600, username, null)) as `<= 10 min`,
  count(username) TotalLogins
from logins
group by username
order by username asc
limit 10;

See SQL Fiddle with Demo. Both queries will give a result:

| USERNAME | <= 10 MIN | TOTALLOGINS |
|    user1 |         4 |           6 |
|    user2 |         3 |           4 |
|    user3 |         1 |           5 |
|    user4 |         4 |           9 |