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.

For 2 same dates there will be 2 amounts which amount will be picked to show single record? — M Khalid Junaid, Sep 18 '17 at 10:55
*"But I want it to be distinct on date."* -- `DISTINCT` is a synonym of [`DISTINCTROW`](https://dev.mysql.com/doc/refman/5.7/en/select.html) and that's how the things work. *"distinct on date"* doesn't make any sense. Which row to choose when there are two or more rows with the same value on the `date` field? — axiac, Sep 18 '17 at 12:06

score 2 · Accepted Answer · answered Sep 18 '17 at 11:02

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

score 0 · Answer 2 · answered Sep 18 '17 at 10:52

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

Can I use Distinct with Union All in MySQL?

2 Answers2