SQL: SELECT COUNT involving a couple of tables

Question

I have a small MS Access database containing four tables and are struggeling with a SELECT COUNT query which involves all tables. Any help will be greatly appreciated.

Overview of the database:

TABLE       COLUMNS

Person         Person_ID |....
Games          Game_ID | Name |....
Played_Games   Played_Games_ID | Game_ID |....
Participation  Played_Games_ID | Person_ID |...

To list out all games and how many times each game is played in total I use:

SELECT DISTINCT
Games.Name,
(SELECT COUNT(*) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS TimesPlayed
FROM Games
ORDER BY Games.Name

This works great, but I would also like to list how many times each game is played by members of a certain group of persons

I have made some progress with the code below (line 4), but I'm still stuck with one small problem:

The reference to Games.Game_ID at the back of the innermost brackets is not linked to the current Game_ID anymore, as it is in the code above. It now seems to be undefined. When I run this within MS Access Query Designer to test, it asks me to type in a value for Games.Game_ID. If I then type in some random Game_ID the code runs "perfectly", but as you probably already figured out, the value of TimesPlayedByGroupMembers will then be the same for every row in the recordset and only true for that one particular Game_ID that I typed in

SELECT DISTINCT
Games.Name,
(SELECT COUNT(*) FROM Played_Games WHERE Played_Games.Game_ID = Games.Game_ID) AS TimesPlayed,
(SELECT COUNT(*) FROM (SELECT DISTINCT Played_Games_ID FROM Participation WHERE Played_Games_ID IN (SELECT Played_Games_ID FROM Played_Games WHERE Game_ID = Games.Game_ID) AND Person_ID IN (26, 27, 28))) AS TimesPlayedByGroupMembers
FROM Games
ORDER BY Games.Name

I will try to explain what my code does and/or is supposed to do from the inside out

The innermost brackets lists out Played_Games_ID's that involves the current game, but this doesn't work because Games.Game_ID is not linked to the current Game_ID

The middle brackets (including the one above) lists out Played_Games_ID's that involves the current game and one or more of the selected persons. The Person_ID's listed here are just an example, and this part of the code is working

The outermost brackets (including all above) counts how many times each game is played by one or more of the selected persons

I'm really stuck with this, so any help will be greatly appreciated

Thanks for your time

This is going to be a slight bit time consuming. Can you post table structures, inserts and desired result example? I will try to break it down when I get home if you are not in hurry. — Bhrugesh Patel, Feb 15 '12 at 17:02
@Bhrugesh Patel Thanks for your interest in helping me! Maybe I don't understand quite what you're asking for though, cause I've already listed the table names, their columns that are involved and what I want listed out(?). GarethD has already solved the task though, but I fear I might run into some problems incorporation his code in the bigger picture. There are more tables and colums involved in my real queries you see. I just extraced the part or the task that I couldn't figure out how to do on my own — Morten, Feb 16 '12 at 15:40
I wanted the Insert statements so I can use them to inject data & run queries on them. Sometimes query may seem good but when u run them & match with expected results, you will find few extra or missing results. Its always best practice to confirm the result in complex queries instead believe that query is doing what it was suppose to do. — Bhrugesh Patel, Feb 16 '12 at 16:57
Ok, I now see what you meant @Bhrugesh Patel and I see that providing create- and inject queries can make it easier for people to help. I'll keep that in mind for any potensial further questions. It's my first time here ;-) — Morten, Feb 17 '12 at 10:37

Andrew · Answer 1 · 2012-02-15T19:01:44.280

1

Your first query is more usually done thus:

SELECT
Games.Name, COUNT(Played_Games.Game_ID) AS TimesPlayed
FROM Games
INNER JOIN Played_Games ON Played_Games.Game_ID = Games.Game_ID
GROUP BY Games.Name
ORDER BY Games.Name

This might be close to what you're after with your second query:

SELECT
Games.Name, COUNT(Participation.Played_Games_ID) AS TimesPlayedByGroupMembers
FROM Games
INNER JOIN Played_Games ON Played_Games.Game_ID = Games.Game_ID
INNER JOIN Participation ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
WHERE Participation.Person_ID IN (26, 27, 28)
GROUP BY Games.Name
ORDER BY Games.Name

edited Feb 15 '12 at 19:01

answered Feb 15 '12 at 17:22

Andrew

4,574
26
31

1

I don't believe Access' db engine will run either of those queries. Change `JOIN` to `INNER JOIN`, or other desired join type. No `COUNT(DISTINCT something)` in Access. – HansUp Feb 15 '12 at 17:36
1

If you are grouping by name, you are also ordering by name, so Order By is redundant. – Fionnuala Feb 15 '12 at 20:19
@Andrew - Query 1: INNER JOIN causes only played games to be listed, but changed to LEFT JOIN this query produced the same results as mine. Thanks – Morten Feb 16 '12 at 14:59
@Andrew - Query 2: I follow your query but not MS Access unfortunately. It says "operator missing" and displays the code following the first ON and until WHERE. Thanks for your time though – Morten Feb 16 '12 at 15:04

GarethD · Accepted Answer · 2012-02-16T15:17:05.670

1

My Access syntax is somewhat rusty, and I am not 100% sure of your table structure but I think the below is will work.

SELECT  Games.Name, 
        TimesPlayed,
        TimesPlayedByGroupMembers
FROM    Games
        INNER JOIN 
        (   SELECT  Game_ID, 
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers
            FROM    Played_Games 
                    LEFT JOIN 
                    (   SELECT  Played_games_ID 
                        FROM    Participation 
                        WHERE   Person_ID IN (1, 2) 
                        GROUP BY Played_games_ID 
                    ) AS Participation 
                        ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
            GROUP BY Game_ID
        ) AS Played_Games
            ON Played_Games.Game_ID = Games.Game_ID
ORDER BY Games.Name

ADDENDUM:

To get games that have not been played to show as 0 use the following:

SELECT  Games.Name, 
        IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS TimesPlayed,
        IIF(ISNULL(TimesPlayedByGroupMembers),0,TimesPlayedByGroupMembers) AS TimesPlayedByGroupMembers
FROM    Games
        LEFT JOIN 
        (   SELECT  Game_ID, 
                    COUNT(*) AS TimesPlayed,
                    SUM(IIF(ISNULL(Participation.Played_Games_ID),0,1)) AS TimesPlayedByGroupMembers
            FROM    Played_Games 
                    LEFT JOIN 
                    (   SELECT  Played_games_ID 
                        FROM    Participation 
                        WHERE   Person_ID IN (1, 2) 
                        GROUP BY Played_games_ID 
                    ) AS Participation 
                        ON Participation.Played_Games_ID = Played_Games.Played_Games_ID
            GROUP BY Game_ID
        ) AS Played_Games
            ON Played_Games.Game_ID = Games.Game_ID
ORDER BY Games.Name

edited Feb 16 '12 at 15:17

answered Feb 15 '12 at 18:23

GarethD

68,045
10
83
123

@Andrew You're not rusty at all my friend :-) My brain has some problems processing all this, but not MS Access :-) It worked great. This will list only played games though, but changing INNER to LEFT fixed that. The only thing that is missing is output of zero's for those rows. I might manage without this, but do you happen to you have any adjustments for that? – Morten Feb 16 '12 at 15:09
@Morten I've added another query to show what I think you are after. – GarethD Feb 16 '12 at 15:18
I apologise GarethD. The comment above was indeed for you, not Andrew. I tried editing it but the time had passed the 5 minutes limit, so I wasn't able. It's my first time on stackoverflow so the interface is still a bit new to me. Sorry – Morten Feb 16 '12 at 15:48
Thanks again GarethD :-) I tried IIF(ISNULL.. myself, further inside the code, but your solutions was much better – Morten Feb 16 '12 at 15:55
No worries. Hopefully this will at least get you on your way to solving the problem. – GarethD Feb 16 '12 at 15:59
You've now perfectly solved what I asked for GarethD, and I thank you for that. I fear I might run into some problems incorporation this code in the bigger picture though, but that's not your problem. There are more tables and colums involved in my real queries you see. I just extraced the part or the task that I couldn't figure out how to do on my own – Morten Feb 16 '12 at 15:59
Because I just started using stackoverflow and only have 6 in reputation I'm sorry that I'm not able to mark your answer as useful (this requires at least 15). But it was indeed – Morten Feb 16 '12 at 16:03
1

No problem. I think you can mark it as the right answer though (click the green tick next to it). This should at least help others viewing the page. As a tip for incorporating this into further queries you could save it as a query then reference the query in other queries, if you save it as `[GamesPlayedCount]` you can then just write queries like `SELECT * FROM [GamesPlayedCount]` rather than rewriting the whole query and adding it to more SQL which can get messy very quickly, especially with the access query editor. – GarethD Feb 16 '12 at 16:12
Good last tip there GarethD. Thanks again – Morten Feb 16 '12 at 16:22
Just a final small comment from me on the addendum query. I had to change `IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS TimesPlayed` to `IIF(ISNULL(TimesPlayed),0,TimesPlayed) AS SomethingElse` to solve a circle reference complaint by MS Access. I also found out that storing the query probably wouldn't help me after all in the bigger picture, as the Person_ID's involved are not static. They change. Beside that, your code helped me a lot and solved what I was asking for, so I'm not at all complaining ;-) – Morten Feb 17 '12 at 10:58
Hello again @GarethD. I'd really appreciate if you have the time to take a look at [this question](http://stackoverflow.com/questions/9412303/sql-select-maxdate-involving-a-couple-of-tables) also. It's kind of a follow-up question – Morten Feb 23 '12 at 11:43

SQL: SELECT COUNT involving a couple of tables

2 Answers2

Linked