Return field different from the one inserted in the GROUP BY clause

Question

I have the following table:

| id | payment_id | user_id | device | 
 ------------------------------------
  1  |     2     |     1   | active |
 ------------------------------------
  2  |     4     |     5   | not_active
 ------------------------------------
  3  |     8     |     1   | not_active 
------------------------------------
  4  |    10     |     5   | active |
 ------------------------------------
  5  |    12     |     1   | active
 ------------------------------------
  6  |    14     |     1   | active
------------------------------------
  7  |    16     |     3   | active |
 ------------------------------------
  8  |    18     |     7   | active
 ------------------------------------
  9  |    20     |     7   | active
 ------------------------------------

I would like to make a query that returns the payment_id only of users that has more than 2 device equal to active.

I thought about it:

SELECT payment_id 
FROM table
GROUP BY user_id
HAVING count(device) > 2

In this case I would return payment_ids 2, 12 and 14 user_id equal to 1.

Someone knows how to do it ?

Answer 1

The query was "almost ready", missed using the WHERE to return only records with device='active' and in the HAVING count more than 2 occurrences, but this would not return the 2, 12 and 14, would return also the 8.

First, you need to do a query that returns "all user_id, which have more than two device='active'", that it would be something like this:

 select user_id
   from teste
  where device='active'
  group by user_id 
 having count(user_id) > 2;

This would return user_id=1. But not all records with user_id=1 are active, for this, let’s do another query, using this previous one by base, and filter only device='active':

 select payment_id 
   from teste
  where device='active'
    and user_id in (
     select user_id
       from teste
      where device='active'
      group by user_id 
     having count(user_id) > 2)
  and device='active';

You can see running here: http://sqlfiddle.com/#! 9/58a17e/6