[SOLVED] Some fun MySQL

[gevans]

I'm doing some reporting for a company, one of the reports is 'Sessions attended ranking list (Top 50)'. My query for this is;

 

SELECT `sessionname`, COUNT(`tabbookings`.`id`) AS book_count
FROM `tabsessions`, `tabbookings`
WHERE `tabsessions`.`id`=`tabbookings`.`sessionid` AND `tabbookings`.`attended` = 1
GROUP BY `sessionname`
ORDER BY `book_count` DESC, `sessionname`
LIMIT 50

 

This works beautifully (I'm hoping not to disclose the table structures), but I want to work out percentages for each group as well. Can anyone see a way of doing this within the single query? (remembering that there is more than 50 sessions, and they all need to be involved in the percentage calculation).

 

My thought is to count all bookings that have been attended in a different query and just do a simple equation from that.

 

<?php
$attended_sessions = 2500; //from database
$first_result = 20; //from database

$percentages = (100/2500)*20;//run this for all 50

 

 

anybody got a better solution?

[gevans]

Well, I think I've got it in a single query, though I still need to do the;

 

$percentages = (100/2500)*20;//run this for all 50

 

For anyone that's interested here's the query;

 

SELECT `sessionname`, COUNT(`tabbookings`.`id`) AS book_count,
(SELECT COUNT(`tabbookings`.`id`) 
FROM tabbookings, tabsessions 
WHERE `tabsessions`.`id`=`tabbookings`.`sessionid` 
AND `tabbookings`.`attended` = 1) AS tot
FROM `tabsessions`, `tabbookings`
WHERE `tabsessions`.`id`=`tabbookings`.`sessionid` AND `tabbookings`.`attended`=1
GROUP BY `sessionname`
ORDER BY `book_count` DESC, `sessionname`
LIMIT 50