mysql sort group by total and name not working -

mysql sort group by total and name not working -

i have php programme outputs names corresponding events attended , number of times each event attended on period of time. illustration of output

name | run | swim | bike | total john 3 2 5 10

mysql query this:

$sql = 'select e.name leader, sum(case when c.catid = 26 1 else null end) "swim", sum(case when c.catid = 25 1 else null end) "bike", sum(case when c.catid = 24 1 else null end) "run", count("swim"+"bike"+"run") total events e left bring together event_categories c on c.uid = e.uid (date(e.event_start) between "'.$from_date.'" , "'.$to_date.'") grouping leader rollup;';

this works well, however, if want sort info "total" in descending order no output if replace lastly grouping line following:

grouping total desc, leader rollup;';

so listing names have highest totals lowest, , people same totals listed in alphabetical order. doing wrong?

as mentioned in comments, order by , rollup can not used together. states here (http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html) half way downwards page. around this, you'll have order in query original query acts subquery:

select * ( select e.name leader, sum(case when c.catid = 26 1 else null end) "swim", sum(case when c.catid = 25 1 else null end) "bike", sum(case when c.catid = 24 1 else null end) "run", count("swim"+"bike"+"run") total events e left bring together event_categories c on c.uid = e.uid (date(e.event_start) between "'.$from_date.'" , "'.$to_date.'") grouping leader rollup ) rolldup order total desc

original (wrong) answer:

you not set sorts in group by clause. set them in order by clause:

$sql = 'select e.name leader, sum(case when c.catid = 26 1 else null end) "swim", sum(case when c.catid = 25 1 else null end) "bike", sum(case when c.catid = 24 1 else null end) "run", count("swim"+"bike"+"run") total events e left bring together event_categories c on c.uid = e.uid (date(e.event_start) between "'.$from_date.'" , "'.$to_date.'") grouping leader rollup order total desc;';

mysql