Record Count in results

[PHP_Idiot]

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?

[Barand]

You require a similar solution to this

http://forums.phpfreaks.com/topic/291228-fetch-categories-and-get-their-last-article-in-same-query/?do=findComment&comment=1491852

[PHP_Idiot]

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

[CroNiX]

Show your new code and post the errors you are getting.

[Barand]

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;

[PHP_Idiot]

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!!