Counting number of positive and negative values group by some column mysql query

Question

I have following layout of a mysql table:

+------+-------+-----+------+
| user |subject|month|change|
+------+-------+-----+------+
|Donald| Math  | Jan | 3.15 |
+------+-------+-----+------+
| Mike | Math  | Jan | 2.15 |
+------+-------+-----+------+
|Regan | Math  | Jan | 3.00 |
+------+-------+-----+------+
|Donald| Engl  | Febr|-3.05 |
+------+-------+-----+------+
| Mike | Engl  | Febr| 3.00 |
+------+-------+-----+------+
|Regan | Engl  | Febr|-3.00 |
+------+-------+-----+------+
|Donald| Geog  | Jan | 3.00 |
+------+-------+-----+------+
| Mike | Geog  | Jan |-2.15 |
+------+-------+-----+------+
|Regan | Geog  | Jan | 3.60 |
+------+-------+-----+------+

I have to count positive or negative change group by subject, at the same time get the name of the group. If I query the mysql table like this:

COUNT (*) FROM $table WHERE change>0 GROUP BY subject

It will only count the number of positive change (>0) group by subject column. It is not possible to get number of negative change (<0) and to get the group name (Math, Eng & Geog).

I want the result should be like that:

===============
Group | >0| <0|
===============
 Math | 3 | 0 | 
---------------
 Eng  | 1 | 2 |
---------------
 Geog | 2 | 1 |
---------------

What would be single mysql query in order to get the result like above?

Answer 1

The query below will group the records by subject. MySQL supports boolean arithmetic since a.change > 0 returns 1 for true and 0 for false.

SELECT  a.Subject,
        SUM(a.change > 0) `> 0`,
        SUM(a.change < 0) `< 0`
FROM    tableName a
GROUP   BY a.Subject

• SQLFiddle Demo

note, it doesn't include subjects where change = 0.