How can I subtotal these results?

[PHP_Idiot]

Hi 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-01-01' AND '2015-01-01'
ORDER BY MembershipNo, venue

which gives me these results: 

each line here represents the result for that person in one game, the more games they play the more their name appears.

 

I need this information to remain in this format for various other functions, however, I'd like to add a column to the end that shows each persons total number of games.

 

like this:

 

I appreciate that this will mean that every line that starts with the same MembershipNo will end in the same total, but I can live with that

 

I've tried using COUNT & ROLLUP but that didn't work in the way I wanted it too!

 

Any ideas?

Edited January 31, 2014 by PHP_Idiot

[Barand]

If all you wanted to do was count the number of games for each player, what would the query be?

 

If all you wanted to do was count the number of games for each player at each venue, what would the query be?

 

I have to ask because I don't know what your original tables look and the structure of your database.

[PHP_Idiot]

If all you wanted to do was count the number of games for each player, what would the query be?

SELECT Player.MembershipNo, COUNT( Results.Date ) 
FROM Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Venue.VenueID = Results.VenueID
AND Results.Date
BETWEEN '2014-01-01'
AND '2015-01-01'
GROUP BY MembershipNo

Gives the following:

MembershipNo

COUNT( Results . Date )

0101001

3

0101002

3

0101004

3

0101032

8

0101033

3

 

 

If all you wanted to do was count the number of games for each player at each venue, what would the query be?

SELECT Player.MembershipNo, COUNT( Results.Date ) , Venue.VenueName
FROM Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Venue.VenueID = Results.VenueID
AND Results.Date
BETWEEN '2014-01-01'
AND '2015-01-01'
GROUP BY MembershipNo, VenueName

Gives this:

MembershipNo

COUNT( Results . Date )

VenueName

0101001

3

The Globe (Weds)

0101002

3

The Globe (Weds)

0101004

1

The Globe (Mon)

0101004

2

The Globe (Weds)

0101032

3

The Crown

0101032

2

The Globe (Mon)

0101032

3

The Globe (Weds)

[PHP_Idiot]

There are more tables in the database, but these three are the ones used for getting results data:

 

 

Player

 

 

 

Results

 

 

Venue

 

[Barand]

From what I could see, you only needed the results table for both those queries. Try

SELECT Player.MembershipNo , Player.FirstName , Player.LastName,  
    Venue.VenueName as Venue, Results.MemCard, Results.EarlyReg, 
    Position.Points as Venue_Points, Results.Date, tot.PlayerTotal
FROM Player 
    INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo
    INNER JOIN Venue ON Venue.VenueID = Results.VenueID
    INNER JOIN Position ON Results.Position = Position.Position
    INNER JOIN (
        SELECT MembershipNo, COUNT(Date) as PlayerTotal
        FROM Results
        GROUP BY MembershipNo
        ) as tot ON Player.MembershipNo = tot.MembershipNo
WHERE Results.Date BETWEEN '2014-01-01' AND '2015-01-01'
ORDER BY MembershipNo, venue

If you want player/venue totals, change the subquery to add in the venue and join on membership and venue

[PHP_Idiot]

Wow - that does exactly what I asked!

However, I wasn't clear in that the total number of games should only count those games played between the given date.

 

So I've modified it like so:

SELECT Player.MembershipNo, Player.FirstName, Player.LastName, 
       Venue.VenueName AS Venue, Results.MemCard, Results.EarlyReg, 
       Position.Points AS Venue_Points, Results.Date, tot.PlayerTotal
FROM Player
       INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo
       INNER JOIN Venue ON Venue.VenueID = Results.VenueID
       INNER JOIN Position ON Results.Position = Position.Position
       INNER JOIN (
             SELECT MembershipNo, COUNT( DATE ) AS PlayerTotal
             FROM Results
             WHERE Results.Date BETWEEN  '2014-01-01' AND '2015-01-01'
             GROUP BY MembershipNo
             ) AS tot ON Player.MembershipNo = tot.MembershipNo
WHERE Results.Date BETWEEN  '2014-01-01' AND  '2015-01-01'
ORDER BY MembershipNo, venue

So my next question is that, although this now gives me exactly the result I want, have I achieved it in the correct way by adding the WHERE clause where I have?

 

and my final question is, what is the advantage of having the inner joins, I've never really understood them properly

 

Thank you for your time and effort I really appreciate you help and am really happy to have it working properly

 

     

[Barand]

Yes, the WHERE is fine.

 

In answer to the other question see http://forums.phpfreaks.com/topic/285539-question-about-inner-join/?p=1465944&do=findComment&comment=1465944

[PHP_Idiot]

Ah got it (I think) thanks for the link