SQL QUERY: compound

There is a query of MySQL. This query can be easily solved in front en开发者_如何学编程d, but need a mechanism in MySQL. how do you count 2 different items in 1 column?? Eg: i have this table :

NAME    FRUIT
A   APPLE
A   APPLE
A   ORANGE
A   APPLE
A   ORANGE
B   APPLE
B   ORANGE
B   ORANGE
B   ORANGE

o/P required:

NAME    No._of_apples   No._of_oranges
A   3            2
B   1            3

Could anyone clarify the code i tried, to obtain this O/P:

select distinct msisdn, count(fruit) no._of_apples, count(fruit) no._of_oranges
from table,

where true

group by 1
order by 1

try

SELECT NAME, 
       SUM(IF(FRUIT = 'APPLE', 1, 0)) AS no_of_apples,
       SUM(IF(FRUIT = 'ORANGE', 1, 0)) AS no_of_oranges
FROM table
GROUP BY NAME
ORDER BY NAME