CODEWITHSUNDEEP
mysqlsql
Mawan
Mawan

Reputation: 147

MySQL Sum operation based on other column

I have a table like this:

Date       | Name  | Pick  | Amount
-----------+-------+-------+-------
2018-01-01 | Alice | Apple |      2
2018-01-01 | Alice | Grape |      3
2018-01-01 | Bob   | Apple |      4
2018-01-02 | Alice | Apple |      5
2018-01-02 | Bob   | Grape |      6

What is the SQL statement that produce result like below?

Name  | Apple | Grape | Total
------+-------+-------+------
Alice |     7 |     3 |    10
Bob   |     4 |     6 |    10

Upvotes: 1

Views: 93

Answers (5)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

the is also a way to calculate your result, using aggregate function and 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

Upvotes: 2

Teneff
Teneff

Reputation: 32148

You can use nested queries. One to calculate the grapes and the apples and one to calculate the total. The advantage is that you have the logic for calculation of each column on a single place and the calculation of the total is a bit more visible

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

db-fiddle

Upvotes: 1

annld
annld

Reputation: 69

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

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Using CASE condition, it will be possible.

Try this:

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

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You want conditional aggregation :

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;

Upvotes: 2

Related Questions