skip to Main Content

I’ve got a simple table that has a column called Date and it stores ‘2015-01-15’ data as an example, it ranges in ‘####-##-##’ and there’s multiple of the same dates, but following it is a number called Amount with values 0, -## and ##.
How would I go through to making it sum up all the negative and positives separately per month?

Example data

[ Amount | Date      ]
[ 0      | 2015-01-01]
[ -15    | 2015-01-09]
[ 566    | 2015-01-15]
[ 6      | 2015-01-25]
[ -2     | 2015-02-01]
[ -15    | 2015-02-09]
[ 10     | 2015-02-15]
[ 6      | 2015-02-25]

So the new data would be in

[ Positives | Negatives | Date   ]
[ 572       | -15       | 2015-01]
[ 16        | -17       | 2015-02]

And so on

2

Answers


  1. Use conditional aggregation:

    select year(date), month(date),
           sum(case when amount > 0 then amount end) as positives,
           sum(case when amount < 0 then amount end) as negatives
    from t
    group by year(date), month(date);
    

    Date functions vary significantly between databases. Although not standard, most support year() and day(). However, you might need different functions for your database.

    Login or Signup to reply.
  2. You can do conditional aggregation; least() and greatest() come handy to shorten the query.

    Assuming that you are running MySQL, like the phpmyadmin tag suggest:

    select
        sum(greatest(amount, 0)) positives,
        sum(least(amount, 0)) negatives,
        date_format(date, '%Y-%m') mon
    from mytable
    group by date_format(date, '%Y-%m')
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search