Mysql count on multiple columns

Question

I have been banging my head against the wall for a few hours on writing this query. I have two tables. I want to select the number of times a game has been played on a certain date but only count one game per user. For example Ping Ping was played 3 times in 2011, but only by 2 users, so the total I want to return is 2.

SESSIONS TABLE

DATE    GAME_ID     USER_ID
2011      1           1
2011      2           1
2011      1           1
2011      1           2
2011      2           2
2012      1           1
2012      1           1

GAMES TABLE

ID      NAME
1       PING PONG
2       TENNIS

I have really struggled so far. This snippet returns the unique times but not limited to date.

SELECT DISTINCT GAME_ID,USER_ID FROM SESSIONS

I know how to limit queries to dates etc but struggling with the count across two columns, do I have to pass the query above into a count function?

Any help would be greatly appreciated!

Thanks

Gordon Linoff · Accepted Answer · 2012-09-05 19:14:54Z

1

What you want to do is to count the distinct users on a given date:

select g.name, count(distinct s.user_id) as NumUsers
from sessions s join
     games g
     on s.game_id = g.id
where date = <whatever your date>
group by g.name

answered Sep 5, 2012 at 19:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

scalabilitysolved Over a year ago

Thank you for the help, you have saved my computer from attempting to fly!

dispake · Accepted Answer · 2012-09-05 19:01:34Z

0

You will want to use the group by clause on user_id

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

answered Sep 5, 2012 at 19:01

dispake

3,3372 gold badges21 silver badges22 bronze badges

Comments

vasja · Accepted Answer · 2012-09-05 19:17:58Z

0

Using inline query:

select DATE, GAME_ID, count(*) from (
SELECT distinct s.DATE, s.GAME_ID, s.USER_ID
FROM SESSIONS s
INNER JOIN GAMES g ON s.GAME_ID =g.ID ) inn
group by DATE, GAME_ID

If needed, add WHERE on DATE to inline query.

answered Sep 5, 2012 at 19:17

vasja

4,79217 silver badges16 bronze badges

Comments

mongotop · Accepted Answer · 2012-09-05 19:21:28Z

0

This query to display the number of users played a specific game in a specific date,

SELECT GAME_ID, USER_ID, COUNT(DISTINCT USER_ID) FROM SESSIONS 
GROUP BY DATE, GAME_ID, USER_ID;

and this query is to display the same result by this time instead of displaying the game_id we display the game_name

SELECT Q2.USER_ID, Q2.nbr_user, GAMES.NAME FROM GAMES, 
         (SELECT GAME_ID, USER_ID, COUNT(DISTINCT USER_ID) as nbr_user
          FROM SESSIONS 
          GROUP BY DATE, GAME_ID, USER_ID) Q2
WHERE GAMES.ID = Q2.GAME_ID

answered Sep 5, 2012 at 19:21

mongotop

5,78415 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Mysql count on multiple columns

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related