MYSQL sorting based on the provided month

Question

I have two tables with a given structure:

Table 1:

ST_Id  ST_Name  
1       xx  
2       yy

Table 2:

AT_Id   AT_Amt  ST_ID   Date

1       500      1     2015-11-17
2       1000     1     2015-11-15
3       300      1     2015-12-1
4       200      2     2015-11-2

I want to get results from mysql order by month. On my php page, I have a table structure for displaying data, as shown below.

State       December    November    September

xx          300         1500          0
yy           0          200           0

Along with these months, I have sorting to sort the amounts by month. Therefore, when I click on the sorting image corresponding to the month of November, it should display the table data in accordance with the quantity order in this column. If I clicked on an upward sort, it should display as

State       December    November    September

yy            0          200         0
xx            300        1500        0

I tried the following query:

SELECT ST.ST_Name,SUM(AT.AT_Amt)     
            FROM `Table2` AS AT 
            LEFT JOIN Table1 AS ST ON AT.ST_Id = ST.ST_Id  
             WHERE AT.Date BETWEEN '2015-04-01' AND '2015-12-31' GROUP BY MONTH( AT.Date) ORDER BY IF(MONTH(AT.Date) = 11 ,  SUM(AT.AT_Amt)  , MONTH( AT.Date)) ASC 

This query returns data as shown below.

ST_Name   SUM(AT.AT_Amt)
xx        300
xx        1700

But the expected result:

ST_Name   SUM(AT.AT_Amt)
yy        200
xx        1500

Can anyone help me fix this? Thanks in advance.

Answer 1

- :

SELECT
    st_name,
    SUM(CASE WHEN MONTH(dt) = 12 THEN at_amt ELSE 0 END) AS December,
    SUM(CASE WHEN MONTH(dt) = 11 THEN at_amt ELSE 0 END) AS November,
    SUM(CASE WHEN MONTH(dt) = 10 THEN at_amt ELSE 0 END) AS September
FROM table2
INNER JOIN table1 ON table1.st_id = table2.st_id
GROUP BY st_name;

PHP, JavaScript , TableSorter . , .

:

+---------+----------+----------+-----------+
| st_name | December | November | September |
+---------+----------+----------+-----------+
| xx      |      300 |     1500 |         0 |
| yy      |        0 |      200 |         0 |
+---------+----------+----------+-----------+

:

SQLFiddle

EDIT:

, , ?

SELECT 
  ST.ST_Name,
  SUM(AT.AT_Amt) as Amount
FROM `Table2` AS AT 
LEFT JOIN Table1 AS ST ON AT.ST_Id = ST.ST_Id  
WHERE AT.dt BETWEEN '2015-04-01' AND '2015-12-31' 
AND MONTH(AT.dt) = 11
GROUP BY ST.ST_Name
ORDER BY Amount

: http://sqlfiddle.com/#!9/51c7c/7

:

| st_name | Amount |
|---------|--------|
|      yy |    200 |
|      xx |   1500 |