Jump to content

Record Count in results


PHP_Idiot
Go to solution Solved by Barand,

Recommended Posts

First up quick thanks in advance, any and all help is greatfully recieved :)

I have this query:

SELECT Player.MembershipNo , Player.FirstName , Player.LastName
, Venue.VenueName as Venue, Results.MemCard, Results.EarlyReg
, Position.Points as Venue_Points, Results.Date
FROM Position , Player , Results , Venue 
WHERE Player.MembershipNo =Results.MembershipNo 
AND Results.Position =Position.Position 
AND Venue.VenueID =Results.VenueID 
AND Results.Date BETWEEN '2014-07-01' AND '2014-09-30' 
ORDER BY MembershipNo, Venue

which returns these results:

+--------------+-----------+----------+-------------------+---------+----------+--------------+------------+
| MembershipNo | FirstName | LastName |       Venue       | MemCard | EarlyReg | Venue_Points |    Date    |
+--------------+-----------+----------+-------------------+---------+----------+--------------+------------+
|            0 | Bob       | Stevens  | The Dolphin       |       1 |        1 |           32 | 27/08/2014 |
|            0 | Bob       | Stevens  | The Enigma Tavern |       1 |        1 |           40 | 08/07/2014 |
|            0 | Bob       | Stevens  | The Enigma Tavern |       1 |        1 |           16 | 15/07/2014 |
|            1 | Dave      | Green    | The Dolphin       |       1 |        1 |           20 | 13/08/2014 |
|            1 | Dave      | Green    | The Dolphin       |       1 |        1 |            2 | 20/08/2014 |
+--------------+-----------+----------+-------------------+---------+----------+--------------+------------+

I would like to ALSO see the total number of times a membership number appears in the results table, not a COUNT DISTINCT because that would give me this:

+--------------+-----------------+-----------+----------+-------------+---------+----------+--------------+------------+
| MembershipNo | MembershipCount | FirstName | LastName |    Venue    | MemCard | EarlyReg | Venue_Points |    Date    |
+--------------+-----------------+-----------+----------+-------------+---------+----------+--------------+------------+
|            0 |               3 | Bob       | Stevens  | The Dolphin |       1 |        1 |           32 | 27/08/2014 |
|            1 |               2 | Dave      | Green    | The Dolphin |       1 |        1 |           20 | 13/08/2014 |
+--------------+-----------------+-----------+----------+-------------+---------+----------+--------------+------------+

 

What I want is the original data with the Membership Count on EVERY line like this:

+--------------+-----------------+-----------+----------+-------------------+---------+----------+--------------+------------+
| MembershipNo | MembershipCount | FirstName | LastName |       Venue       | MemCard | EarlyReg | Venue_Points |    Date    |
+--------------+-----------------+-----------+----------+-------------------+---------+----------+--------------+------------+
|            0 |               3 | Bob       | Stevens  | The Dolphin       |       1 |        1 |           32 | 27/08/2014 |
|            0 |               3 | Bob       | Stevens  | The Enigma Tavern |       1 |        1 |           40 | 08/07/2014 |
|            0 |               3 | Bob       | Stevens  | The Enigma Tavern |       1 |        1 |           16 | 15/07/2014 |
|            1 |               2 | Dave      | Green    | The Dolphin       |       1 |        1 |           20 | 13/08/2014 |
|            1 |               2 | Dave      | Green    | The Dolphin       |       1 |        1 |            2 | 20/08/2014 |
+--------------+-----------------+-----------+----------+-------------------+---------+----------+--------------+------------+

Any ideas how I can do this?

Link to comment
Share on other sites

Hi Barand,

Thanks for the link, I've had a close look at it and tried to make it work for my situation, but I'm just getting errors around the field names, I was hoping this would be and easy additional to the existing query, but it's looking like that wont be the case!

 

Any chance you could expand a bit on how I can get that to work in my situation please, feel free to over simplify because I'm not really sure what I'm doing at all here!!!

 

Thanks a lot

Link to comment
Share on other sites

  • Solution

Use explicit JOIN syntax and not "FROM A,B,C WHERE..."

 

It separates the structure of your query from the selection criteria and it is more efficient.

 

I have incorporated the subquery for you get the counts

SELECT Player.MembershipNo
, num.MembershipCount
, Player.FirstName 
, Player.LastName
, Venue.VenueName as Venue
, Results.MemCard
, Results.EarlyReg
, Position.Points as Venue_Points
, Results.Date
FROM Player
    INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo
    INNER JOIN Position ON Results.Position = Position.Position
    INNER JOIN Venue ON Venue.VenueID = Results.VenueID
    INNER JOIN (
            SELECT MembershipNo
            , COUNT(*) as MembershipCount
            FROM Results
            GROUP BY MembershipNo
            ) num ON Player.MembershipNo = num.MembershipNo
WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30' 
ORDER BY Player.MembershipNo, Venue;
  • Like 1
Link to comment
Share on other sites

Hi Barand,

Thank you so much!

 

I see now where I was going wrong, I wasn't using the correct JOIN Syntax (as you correctly suspected),

I've added a slight amendment to limit the MembershipCount to the same date range, your version counts all games over all time, but I need it limited to the same time period as the reported games, I suspect that is far more likely to be bad explanation on my part than anything else though ;)

 

This is the one line update:

SELECT Player.MembershipNo
, num.MembershipCount
, Player.FirstName 
, Player.LastName
, Venue.VenueName as Venue
, Results.MemCard
, Results.EarlyReg
, Position.Points as Venue_Points
, Results.Date
FROM Player
    INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo
    INNER JOIN Position ON Results.Position = Position.Position
    INNER JOIN Venue ON Venue.VenueID = Results.VenueID
    INNER JOIN (
            SELECT MembershipNo
            , COUNT(*) as MembershipCount
            FROM Results
        WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30'
            GROUP BY MembershipNo
                    ) num ON Player.MembershipNo = num.MembershipNo
WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30'
ORDER BY Player.MembershipNo, Venue

Thanks you so much, I've been battling with this for over a week now. It always looks so simple when it's finally working!!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.