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?