mysql group by single column but aggregate on multiple columns?

Question

Below is the big query, it works for 4 columns ( 4 virtual tables ) but not for 12 columns, what would be the better way to do it.

Below is query for 4 columns,

select tbl1.count1, tbl2.fresh1, tbl3.new1, tbl4.score1, tbl1.user_id from 
(
    (select count(id) as count1, user_id 
        from user_ranking 
        where rank = 1 
        group by user_id
    ) as tbl1 
 JOIN 
    (select count(id) as fresh1, user_id 
        from user_ranking 
        where rank = 1 and is_fresh = 1 
        group by user_id
    ) as tbl2 ON tbl1.user_id = tbl2.user_id 
 JOIN 
    (select count(id) as new1, user_id 
        from user_ranking 
        where rank = 1 and is_new = 1 
        group by user_id
    ) as tbl3 ON tbl2.user_id = tbl3.user_id 
 JOIN 
    (select AVG(score) as score1, user_id 
        from user_ranking 
        where rank = 1 group by user_id
    ) as tbl4 ON tbl3.user_id = tbl4.user_id
);

Above works fine for me but doesnt work for 12 columns.

As suggested in below answer I tried

select user_id, SUM(rank = 1 ) as count1, SUM(rank = 1 and is_fresh = 1) as fresh1, SUM(rank = 1 and is_new = 1 ) as new1, 
SUM(IF(rank=1, score, 0))/SUM(rank=1) as score1 from user_ranking group by user_id;

It gives different result that above 4 column query.

Below is query for 12 column which does not work

select tbl1.user_id,
tbl1.count1, tbl2.fresh1, tbl3.new1, tbl4.score1, 
tbl5.count2, tbl6.fresh2, tbl7.new2, tbl8.score2,  
tbl9.count3, tbl10.fresh3, tbl11.new3, tbl12.score3  
from 
(
    (select count(id) as count1, user_id 
        from user_ranking 
        where rank = 1 
        group by user_id
    ) as tbl1 
 JOIN 
    (select count(id) as fresh1, user_id
        from user_ranking 
        where rank = 1 and is_fresh = 1 
        group by user_id
    ) as tbl2 
 JOIN 
    (select count(id) as new1, user_id
        from user_ranking 
        where rank = 1 and is_new = 1 
        group by user_id
    ) as tbl3 
 JOIN 
    (select AVG(score) as score1, user_id
        from user_ranking 
        where rank = 1 group by user_id
    ) as tbl4 
 JOIN  
    (select count(id) as count2, user_id
        from user_ranking 
        where rank = 2 
        group by user_id
    ) as tbl5 
 JOIN 
    (select count(id) as fresh2, user_id
        from user_ranking 
        where rank = 2 and is_fresh = 1 
        group by user_id
    ) as tbl6 
 JOIN 
    (select count(id) as new2, user_id
        from user_ranking 
        where rank = 2 and is_new = 1 
        group by user_id
    ) as tbl7 
 JOIN 
    (select AVG(score) as score2, user_id 
        from user_ranking 
        where rank = 2 group by user_id
    ) as tbl8
JOIN  
    (select count(id) as count3, user_id
        from user_ranking 
        where rank = 3
        group by user_id
    ) as tbl9 
 JOIN 
    (select count(id) as fresh3, user_id
        from user_ranking 
        where rank = 3 and is_fresh = 1 
        group by user_id
    ) as tbl10 
JOIN 
    (select count(id) as new3, user_id
        from user_ranking 
        where rank = 4 and is_new = 1 
        group by user_id
    ) as tbl11 
JOIN 
    (select AVG(score) as score3, user_id
        from user_ranking 
        where rank = 3 group by user_id
    ) as tbl12    
);

Table schema

CREATE TABLE IF NOT EXISTS `user_ranking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  `is_fresh` int(11) NOT NULL,
  `is_new` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `user_ranking`
--

INSERT INTO `user_ranking` (`id`, `user_id`, `rank`, `is_fresh`, `is_new`, `score`) VALUES
(1, 1, 1, 1, 0, 25),
(2, 2, 1, 1, 0, 67),
(3, 1, 2, 0, 1, 34),
(4, 2, 2, 0, 1, 24),
(5, 2, 1, 0, 1, 36),
(6, 1, 2, 1, 1, 1),
(7, 1, 1, 0, 1, 75);

Answer 1

just move condition from every WHERE to SUM

SELECT 
    user_id, 
    SUM(rank = 1) AS rank1, 
    SUM(rank = 2) AS rank2, 
    SUM(rank = 2 and is_fresh = 1 ) AS rank2_fresh,
    SUM(IF(rank = 1, age, 0))/SUM(rank = 1) AS rank_1_avg_age
    ... 
FROM users
GROUP BY user_id