CODEWITHSUNDEEP
mysqlsql
rick jan cawaling
rick jan cawaling

Reputation: 440

Two where "date" clause to have different column

I'm trying to have 2 different columns with different date in mysql

For example I have this table

id     name     amount     date

1      Jane     20.00      2015-08-14
2      Joe      10.00      2015-08-15
3      Joe      20.00      2015-08-21
4      Jane     30.00      2015-09-21
5      Joe      20.00      2015-09-25
6      Jane     10.00      2015-09-25

I want to get the sum of amount groupby name and display two columns for different date one is where date <= '2015-08-31' and the other one is where date <= '2015-09-30'

My desired output is

id    name    amount_aug    amount_sep
1     Jane    20.00         60.00
2     Joe     30.00         50.00

My query so far is select name, SUM(amount) amount_sum from table_name groupby name which will simply output

name     amount_sum
Jane     60.00
Joe      50.00

Upvotes: 0

Views: 53

Answers (3)

daniel
daniel

Reputation: 1070

Here is an example:

select
    name,
    sum(case when date <= '2015-08-31' then amount else 0 end) amount_on_or_before_aug,
    sum(case when date <= '2015-09-30' then amount else 0 end) amount_on_or_before_sept
from table_name
group by name

Upvotes: 2

Esteban Santini
Esteban Santini

Reputation: 465

SELECT a.id, a.name, sum(b.amount) AS 'amount_aug', sum(a.amount) AS 'amount_sep'
FROM table_name a
LEFT JOIN table_name b ON a.id = b.id AND b.date <= '2015-08-31'
WHERE a.date <= '2015-09-30'
GROUP BY a.name

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

SELECT
  name,
  SUM(
    IF( date <= '2015-08-31', amount, 0 )
  ) amount_aug,
  SUM(
    IF( date <= '2015-09-30', amount, 0 )
  ) amount_sep
FROM table_name
GROUP BY name

Upvotes: 3

Related Questions