MySQL Subquery using SUM, WHERE, GROUP_BY

Question

I'm a beginner (not a DBA). The simple version of my data ==> My hoped for result:

|ball |color|count|         |ball |Total Blue|Total Red| 
-------------------         ----------------------------
|b1   |red  |   2 |  ====>  |b1   | 5       | 2        |  
|b1   |blue |   3 |         |b2   | 3       | 1        |  
|b1   |blue |   2 |           
|b2   |red  |   1 |             
|b2   |blue |   3 |     

I want to tabulate each ball (b1, b2, etc). Then the total instance of each color for each ball. There are multiple entries for each color of each ball (in my real world data). But here I show multiple entries for the blue #1-balls only.

I can easily do this:

SELECT ball, 
SUM(count) AS 'Total Blue'
FROM t1
WHERE color = 'blue'
GROUP BY ball

To obtain the first (good) result:

|ball |Total Blue| 
 -----------------
|b1   | 5        |  
|b2   | 3        | 

To go further, I think I need a subquery. But I have not been able to get the subquery to process the same way as the regular query.

Below is the closest result I've gotten so far (simple attempt):

SELECT ball, 
SUM(count) AS 'Total Blue',
(SELECT SUM(count) FROM t1 WHERE color = 'red') AS 'Total Red'
FROM t1 
WHERE color = 'blue'
GROUP BY ball

I get this:

|ball |Total Blue| Total Red|
 ---------------------------
|b1   | 5        |  3       |
|b2   | 3        |  3       |

Total Red shows the total of all red balls regardless of ball number.

This more involved subquery (for red) produces the exact same result:

(SELECT SUM(cc) FROM 
(SELECT DISTINCT count AS cc FROM t1  WHERE color = 'red') AS dd )
AS 'Total Red'

I've added GROUP BY to this subquery to no added effect. This is as close as I've been able to get. Many other tries have given a variety of results.

Answer 1

Try combining SUM and IF:

 SELECT
    ball, 
    SUM(IF(color = 'blue', count, 0)) AS 'Total Blue'
    SUM(IF(color = 'red', count, 0)) AS 'Total Red'
 FROM t1
 GROUP BY ball

Answer 2

I just want to offer the following, because CASE is standard SQL and IF is not:

select ball,
       sum(case when color = 'blue' then `count` else 0 end) as TotalBlue,
       sum(case when color = 'red' then `count` else 0 end) as TotalRed
from t
group by ball
order by 1

Also, having "count" as the name of a column is a bad idea, because it is an SQL reserved word.