Can I use Distinct with Union All in MySQL?

Question

I am getting values from different tables using UNION ALL. My code is as below.

SELECT DISTINCT datei, amount FROM
(
    SELECT datei, amount, 1 AS identification FROM income
    UNION ALL
    SELECT datee, amount, 2 AS identification FROM expense
    UNION ALL
    SELECT date, amount, 3 AS identification FROM others
) t
ORDER BY `datei` ASC

But I want it to be distinct on date. So how can I do that? Thanks in advance.

Answer 1

To get single amount for date you can use following query

SELECT  datei,SUBSTRING_INDEX(GROUP_CONCAT(amount),',',1) amount
FROM (
    SELECT datei,amount, 1 AS identification FROM income
    UNION ALL
    SELECT datee,amount, 2 AS identification FROM expense
    UNION ALL
    SELECT DATE,amount, 3 AS identification FROM others
) t
GROUP BY datei
ORDER BY `datei` ASC

Answer 2

SELECT datei, sum(amount)
FROM
(
    SELECT datei, amount, 1 AS identification FROM income
    UNION ALL
    SELECT datee, amount, 2 AS identification FROM expense
    UNION ALL
    SELECT date, amount, 3 AS identification FROM others
) t
GROUP BY datei
ORDER BY datei ASC