CODEWITHSUNDEEP
mysql
user3316
user3316

Reputation: 43

MySQL: Select only some grouped rows

I have 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 am not sure how to select and group usernames which have the same action (=login) and points higher than 5 (for all actions).

Expected results:

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

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

Sorry, I did not emphasize the fact, that all actions has to be login. So, results will be without john, because there is action comment related to this username.

Upvotes: 4

Views: 139

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can do this with an aggregation and a clever having clause:

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'

Upvotes: 0

Meherzad
Meherzad

Reputation: 8563

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 |

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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 on (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 ;

Upvotes: 3

Related Questions