MYSQL sorting based on provided month

Question

I have two tables with 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. In my php page, I have a table structure for displaying data like shown below

State       December    November    September

xx          300         1500          0
yy           0          200           0

Along with these months, I have a sorting link for sorting the amounts based on month. So when I click the sorting image corresponding to the month November, it should list the table data according to the order of amount in that column. If I clicked for ascending sorting it should list like,

State       December    November    September

yy            0          200         0
xx            300        1500        0

I tried with 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 is returning data like shown below.

ST_Name   SUM(AT.AT_Amt)
xx        300
xx        1700

But the expected result is :

ST_Name   SUM(AT.AT_Amt)
yy        200
xx        1500

Can anyone please help me fix this? Thanks in advance.

related(?): http://stackoverflow.com/questions/7674786/mysql-pivot-table — , Dec 18 '15 at 05:49
Should the expected output for column xx not be 1800(1500 + 300)? — Maulik Shah, Dec 18 '15 at 06:20
Handle the sorting part in your application code, with a bit of JavaScript — Strawberry, Dec 18 '15 at 07:08

zedfoxus · Accepted Answer · 2015-12-18T06:22:11.700

5

You can do something like this:

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;

Once you get data into PHP, you could just use JavaScript to do client-side sorting using TableSorter or similar library. That way sorting doesn't incur the expense of re-querying the database.

Result of your data will be:

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

Click the link below for a running demo:

SQLFiddle

EDIT:

Based on your comment, is this something you need?

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

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

Result:

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

edited Dec 18 '15 at 06:22

answered Dec 18 '15 at 05:36

zedfoxus

35,121
5
64
63

@Tareq I think you are misunderstanding how a pivot works. Each record from a given month will appear in a separate column. – Tim Biegeleisen Dec 18 '15 at 05:47
@TimBiegeleisen Hm. Got it now. – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Dec 18 '15 at 05:49
@zedfoxus..I don't need all months in this query. I want the states based on order of month (for selected month) followed by other states (for other months). Only one field corresponding to amount is needed. – Jenz Dec 18 '15 at 06:13
@Jenz I have updated my answer. Is that what you want? – zedfoxus Dec 18 '15 at 06:22
This solution makes no sense in the context of a data grid like the one proposed by the op – Strawberry Dec 18 '15 at 07:09

MYSQL sorting based on provided month

1 Answers1