您需要条件聚合：

select name, 
       sum(case when pick = 'Apple' then amount else 0  end) Apple,
       sum(case when pick = 'Grape' then amount else 0  end) Grape,
       sum(case when pick in ('Apple', 'Grape') then amount else 0 end) Total
from table t
group by name;

SELECT Name, 
sum(if(Pick='Apple', Amount, 0)) as Apple, 
sum(if(Pick='Grape', amount, 0)) as Grape 
FROM table_name
GROUP BY Name;

使用 CASE 条件，这是可能的。
试试这个：

select name
    ,sum(case when pick = 'Apple' then amount end)Apple
    ,sum(case when pick = 'Grape' then amount end)Grape
    ,sum(case when pick = 'Apple' then amount end)
        +sum(case when pick = 'Grape' then amount end)Total
from your_table
group by name

使用聚合函数和 or ```
select name,
sum(case when pick = 'Apple' then amount else 0 end) Apple,
sum(case when pick = 'Grape' then amount else 0 end) Grape,
sum(case when pick in('Apple','Grape') then amount else 0 end) Total
from tableA
group by name

http://sqlfiddle.com/#!9/4c56c/4号

可以使用嵌套查询。一个用来计算葡萄和苹果，一个用来计算总数。优点是，您可以在一个地方计算每一列的逻辑，并且可以更直观地计算总数

SELECT 
    Name,
    Apple,
    Grape,
    Apple + Grape as Total
FROM (
  SELECT
      `name` as Name,
      SUM(IF(pick = 'Apple', amount, 0)) as Apple,
      SUM(IF(pick = 'Grape', amount, 0)) as Grape
  FROM test
  GROUP BY name
) AS t1

小提琴