SQL: Help Doing Two Counts In One Query

Question

I have a table that looks like so:

| user_id | client_application_id | invalidated_at
|       1 | 55555 | |
|       1 | 123   | |

I want to be able to do one thing:

Be able to count how many user_id's have more than 250 client_application_id's on their account and order it so that it starts at 250 and goes up.

My current (incorrect)query looks like so:

select  user_id, count(user_id) as cnt from tokens where invalidated_at is null group by user_id having count(user_id) > 250 order by cnt ;

The output looks like so:

  user_id   | cnt 
------------+-----
    1 | 251
    5 | 251

Using this example, I would like the query to count the two users that have 251, so it would look like so:

Count_Of_Users  | Application_Count
2 | 251

what's the problem .. you have the user_id by the firts query .. explain better your expected resul for the secondo query.. — ScaisEdge
– ScaisEdge, Commented Oct 22, 2016 at 19:37

Jayvee · Accepted Answer · 2016-10-22 21:31:31Z

1

You can use something similar to your query as a subquery and get the output you need:

    select count(user_id) as count_of_users, cnt as application_count from
    (
       select  user_id, count(client_application_id) as cnt 
       from tokens 
       where invalidated_at is null 
       group by user_id having count(client_application_id) > 250 
    ) t
    group by cnt
    order by cnt

edited Oct 22, 2016 at 21:31

answered Oct 22, 2016 at 21:25

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2016-10-22 19:40:27Z

0

Is not clear which is your expected result for the secondo query could be somthings like this

  select users.*, t.user_id, t.cnt 
  from users 
  inner join ( 

      select  user_id, count(user_id) as cnt 
      from tokens 
      where invalidated_at is null 
      group by user_id 
      having count(user_id) > 250 
  ) t on users.id = t.user_id 
  order by t.user_id

answered Oct 22, 2016 at 19:40

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

SQL: Help Doing Two Counts In One Query

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