Grouping results from UNION Result-CodePudding

I'm trying to get the messages from a table:

  (SELECT 'MesRec' as Type, u.UserID, u.Name, u.TeamID, max(m.Message) as Message, sum(case when m.DateRead is null then 1 else 0 end) Qty, DateSent
  FROM Messages m
  JOIN Users u on u.UserID = m.UserFrom
  WHERE m.UserTo = 5
  GROUP BY m.UserFrom
  ORDER BY m.DateSent DESC)
UNION ALL
  (SELECT 'MesSen' as Type, u.UserID, u.Name, u.TeamID, max(m.Message) as Message, sum(case when m.DateRead is null then 1 else 0 end) Qty, DateSent
  FROM Messages m
  JOIN Users u on u.UserID = m.UserTo
  WHERE m.UserFrom = 5
  GROUP BY m.UserTo
  ORDER BY m.DateSent DESC)
ORDER BY DateSent

Which is returning the data exactly like I'd expect, however... I'd like to merge data where the UserID matches. I've tried adding a GROUP BY on u.UserID after the UNION, but to no prevail!

Currently I'll get the result of:

MesRec | 4 | Mr D | 4 | Test Message | 1
MesRec | 1 | Mr A | 1 | Test Message | 0
MesSen | 2 | Mr B | 2 | Test Message | 0
MesSen | 4 | Mr D | 4 | Test Message | 0

CodePudding user response：

Wrap the UNION result up as a subquery in the FROM clause of an outer query, and apply the ORDER BY to the outer query only:

SELECT tmp.* FROM (
  (SELECT 'MesRec' as Type, u.UserID, u.Name, u.TeamID, max(m.Message) as Message, sum(case when m.DateRead is null then 1 else 0 end) Qty, DateSent
  FROM Messages m
  JOIN Users u on u.UserID = m.UserFrom
  WHERE m.UserTo = 5
  GROUP BY m.UserFrom
)
UNION ALL
  (SELECT 'MesSen' as Type, u.UserID, u.Name, u.TeamID, max(m.Message) as Message, sum(case when m.DateRead is null then 1 else 0 end) Qty, DateSent
  FROM Messages m
  JOIN Users u on u.UserID = m.UserTo
  WHERE m.UserFrom = 5
  GROUP BY m.UserTo
) tmp
ORDER BY tmp.DateSent