CODEWITHSUNDEEP
mysqlsqldatabase
scalabilitysolved
scalabilitysolved

Reputation: 2474

Mysql count on multiple columns

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

Upvotes: 0

Views: 3906

Answers (4)

mongotop
mongotop

Reputation: 5774

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

Upvotes: 0

vasja
vasja

Reputation: 4792

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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

Upvotes: 1

dispake
dispake

Reputation: 3329

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

Upvotes: 0

Related Questions