phpmyadmin Question posted in
The official documentation can be found here.

How do I sort dates into months and their respective numbers into positives and negatives separately? – Phpmyadmin

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

Tags:

2

Answers


  1. 0

    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. 0

    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