CODEWITHSUNDEEP
mysql
Satu Sultana
Satu Sultana

Reputation: 537

MYSQL union how to maintain date field order when date came from 2 fields?

I have two tables Transactions and Expenses. I have written a query to get date wise transaction statement. Here Transactions table is deposit table. For this query I am getting my desire result without order date. 

SELECT IFNULL(date(t1.created), date(ex.created)) as Date , sum(t1.amount) as ReceiveAmount,ex.amount as ExpensesAmount 
    FROM transactions as t1
    LEFT JOIN (
        SELECT sum(e.amount) as amount, created
           FROM expenses as e 
           group by date(e.created)
        ) as ex
    ON date(ex.created) =  date(t1.created)
    GROUP BY date(t1.created)

    UNION

    SELECT IFNULL(date(t1.created), date(ex.created)) as Date, sum(t1.amount) as Receive,ex.amount as ExpensesAmount 
    FROM transactions as t1

    RIGHT JOIN (
        SELECT sum(e.amount) as amount, created
        FROM expenses as e 
        group by date(e.created)
    ) as ex
    ON date(t1.created) = date(ex.created)
    GROUP BY date(t1.created)

OUTPUT : 

Date       ReceiveAmount    ExpensesAmount  
2018-12-04     600            NULL
2019-08-01     500            NULL
2019-10-18     500            NULL
2019-11-18     820            500  <== that should come at last.
2019-11-04     NULL           100

I need to see date ASC order. Here last 2 date 2019-11-18 and 2019-11-04 not maintaining ORDER. How can I solve this problem ?

Upvotes: 0

Views: 25

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521299

You may add an ORDER BY clause to your union query, after placing both halves of the union in parentheses:

(SELECT IFNULL(t1.created, DATE(ex.created)) AS Date, SUM(t1.amount) AS ReceiveAmount,
     ex.amount AS ExpensesAmount 
FROM transactions as t1
LEFT JOIN
...
)

UNION ALL

(SELECT IFNULL(t1.created, DATE(ex.created)), SUM(t1.amount), ex.amount
FROM transactions as t1
RIGHT JOIN
...
)
ORDER BY Date

I assume here that you really want a UNION ALL, and not a UNION. Note that in most other RDBMS you would have to use a formal subquery to apply an ORDER BY clause to the entire union query.

Upvotes: 1

Related Questions