MySQL: select only some grouped rows

Question

I have the following database (small example):

ID | username | action | points 1 | matt | login | 3 2 | john | comment | 6 3 | john | login | 6 4 | peter | login | 8 5 | peter | login | 8 

And I'm not sure how to select and group user names that have the same action (= login) and points above 5 (for all actions).

Expected results:

 username | COUNT | points(SUM) peter | 2 | 16 

I tried the subquery but did not find the right solution. Do you have any idea how to do this? Thank you so much for your help!

Sorry, I did not emphasize that all actions must be login . Thus, the results will be without john , because there is a comment action associated with this username.

Answer 1

 SELECT username, COUNT(*) AS cnt, SUM(points) AS points FROM tableX AS t GROUP BY username HAVING COUNT(*) = COUNT(CASE WHEN action = 'login' THEN action END) AND SUM(points) > 5 ; 

or

 SELECT username, COUNT(*) AS cnt, SUM(points) AS points FROM tableX AS t WHERE action = 'login' AND NOT EXISTS ( SELECT * FROM tableX AS tt WHERE tt.username = t.username AND ( tt.action <> 'login' OR tt.action IS NULL ) ) GROUP BY username HAVING SUM(points) > 5 ; 

But I think that with an index (username, login) and one on (username, points) this will be the most efficient:

 SELECT username, COUNT(*) AS cnt, SUM(points) AS points FROM tableX AS t GROUP BY username HAVING MIN(action) = 'login' AND MAX(action) = 'login' AND SUM(points) > 5 ; 

Answer 2

You can do this using aggregation and a smart having sentence:

 select username, sum(points), count(*) from tbl group by username having sum(case when points <= 5 then 1 else 0 end) = 0 and -- count number with points < 5 max(action) = min(action) and -- all the actions are the same and min(action) = 'login' 

Answer 3

try this query

 select username, action, sum(points), count(*) from tbl group by username, action having sum(if (points<=5, 1, 0)) =0 and count(*) >=2 

SQL FIDDLE :

 | USERNAME | ACTION | SUM(POINTS) | COUNT(*) | ---------------------------------------------- | peter | login | 16 | 2 |