All INNER JOINs and UNIONs maybe

PROPOSED QUERY

select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
union select t1.id,t2.id,NULL,NULL from t1
    inner join t2 using (id)
union select t1.id,t2.id,t3.id,NULL from t1
    inner join t2 using (id)
    inner join t3 using (id)
union select t1.id,t2.id,t3.id,t4.id from t1
    inner join t2 using (id)
    inner join t3 using (id)
    inner join t4 using (id)
;

SAMPLE DATA

use test
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1
(
    id int not null,
    name varchar(20),
    primary key (id)
); 
insert into t1 values (1,'name1');
create table t2 like t1; insert into t2 values (1,'name2');
create table t3 like t1; insert into t3 values (1,'name3');
create table t4 like t1; insert into t4 values (1,'name4');

PROPOSED QUERY EXECUTED

mysql> select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
    -> union select t1.id,t2.id,NULL,NULL from t1
    ->     inner join t2 using (id)
    -> union select t1.id,t2.id,t3.id,NULL from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    -> union select t1.id,t2.id,t3.id,t4.id from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    ->     inner join t4 using (id)
    -> ;
+-------+-------+-------+-------+
| t1_id | t2_id | t3_id | t4_id |
+-------+-------+-------+-------+
|     1 |  NULL |  NULL |  NULL |
|     1 |     1 |  NULL |  NULL |
|     1 |     1 |     1 |  NULL |
|     1 |     1 |     1 |     1 |
+-------+-------+-------+-------+
4 rows in set (0.00 sec)

mysql>

All the columns have id as a common name. If you want, I can adjust my answer with the id names

It seems to be some old limitation of the optimizer maybe, aliases to aggregating expressions are not allowed in more complex expressions in ORDER BY.

You can get around it by using the full expression instead of the alias:

ORDER BY
    COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END)
  *
    AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)