How do I get the total of this column!?

[PHP_Idiot]

Ok so I have this query:

SELECT Venue.VenueName, Venue.VenueID, COUNT( Results.MembershipNo ) AS  'total', COUNT( DISTINCT Results.Date ) AS  'dates', CAST( COALESCE( COUNT( Results.MembershipNo ) / COUNT( DISTINCT Results.Date ) ) AS DECIMAL( 10, 1 ) ) AS  'Average'
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Venue.active =  'Y'
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
AND Results.Date
BETWEEN  '2013-07-01'
AND CURDATE( ) 
GROUP BY Venue.VenueName
ORDER BY Average DESC 

Which gives this result:

VenueNameVenueIDtotaldatesAverage

The Globe (Weds)22141623.5

The Globe (Weds)22 141 6 23.5
The Tawny Owl 16 118 6 19.7
The Globe (Mon) 30 110 6 18.3
The Duke Of Wellington 28 90 6 15.0
The Mother Redcap 26 57 4 14.3
The Jolly Milliner 23 79 6 13.2
The Old Red Lion 29 545 10.8
The Enigma Tavern 24 65 6 10.8
The Crown 11 62 6 10.3
The Sportsman 31 18 2 9.0
The Heath Inn 33 27 3 9.0

The Nags Head 32 31 4 7.8

 

What I really need now is the total of the 'total' column and the total of the 'dates' column so I can divide 'total'  by 'dates' to get an average.

 

But everything I try fails, I've used the Sum() function but I can't get it to work properly.

Any help would be really appreciated.

 

Cheers

 

[Barrikor]

Have a $totalOfTotals variable that you add to on each fetch.

 

(Assuming that you need to also display the data at the same time)

[Zane]

Try

	GROUP BY Venue.VenueName WITH ROLLUP

[PHP_Idiot]

  On 8/13/2013 at 12:47 AM, Zane said:

Try

	GROUP BY Venue.VenueName WITH ROLLUP

 

I gave this a go but the results are wrong, as you can see from the table section the 'total' total is correct but the 'dates' total is still only adding the Distinct dates together, rather than the subtotal of each row. Is there a way around this?

 

total

dates

Average

860

32

26.9

[PHP_Idiot]

  On 8/13/2013 at 12:30 AM, Barrikor said:

Have a $totalOfTotals variable that you add to on each fetch.

 

(Assuming that you need to also display the data at the same time)

 

I'm not entirely sure what you mean by this or how I would implement it.

I don't have time right now but I will play about with it later this afternoon.

[PHP_Idiot]

  On 8/13/2013 at 12:30 AM, Barrikor said:

Have a $totalOfTotals variable that you add to on each fetch.

 

(Assuming that you need to also display the data at the same time)

 

Had time to play about with it now, and this has worked:

$totalPlayers=$totalPlayers+$r['total'];
$totalDates=$totalDates+$r['COUNT( DISTINCT Results.Date )'];
$AVE=ROUND($totalPlayers/$totalDates,1);

I've now getting exactly the figures I should be, thanks a lot Barrikor