mysql - complex query combining pivot table, count, group, union, frequency, join and order

Question

I have the following table where a1 is the age which can be repeated. g1 is the genders where 0 denotes male and 1 denotes female.

| a1 | g1|    
.................    
|   40 |   0 |    
|   48 |   1 |      
|   47 |   0 | 
|   52 |   0 |       
|   64 |   1 |      
|   50 |   1 |     
|   63 |   1 |     
|   57 |   0 |  

I want to show the number of males and females according to the ages with the sum of males and females per row. I couldn't get the desired result.

| a1 |male|female|total|    
-----------------------------
| 40 | 120 | 80 | 200 |     
| 48 | 130 | 90 | 220 |     
| 47 | 140 | 70 | 210 |      
| 52 | 150 | 40 | 190 |     
| 64 | 160 | 45 | 205 |     
| 50 | 170 | 30 | 200 |     
| 63 | 180 | 30 | 210 |     
| 57 | 190 | 50 | 240 | 

Answer 1

Use CASE WHEN:

select a1 as age, sum(case when g1=0 then 1 end) as male, sum(case when g1=1 then 1 end) as female, sum(case when g1 in (1,0) then 1 end) as total
group by a1