Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
+----------+----------+----------+
share|improve this question

1 Answer 1

up vote 4 down vote accepted

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 |
share|improve this answer
    
Thank you. I wasn't really aware that if or case statements could be used within a count function like that. –  Jacobm001 Sep 3 '13 at 22:24
    
This CASE approach is also good when you need to select which column to use for each row by condition. –  Alexander Shcheblikin Sep 30 '13 at 10:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.