How to group data from two tables by one column?

Question

MySQL

Can you please tell me how can I group data from two tables by value value ?
The tables "data1, data2" store statuses of different types and I need to make a general table where to show how many users have this status.

$query = User::leftJoin('data1', 'data1.user_id', '=', 'users.id')
            ->leftJoin('data2', 'data2.user_id', '=', 'users.id')
            ->select('data1.text as value', 'data2.name as value', DB::raw('count(*) as users_count'))
            ->groupBy('value')
            ->orderByDesc('users_count');

Answer 1

select d.value,
       count(*) users_count 
  from user u

left join (select user_id, data1.text value from data1
            union all
           select user_id, data2.name value from data2
           ) d on d.user_id = u.id

group by d.value

Answer 2

late...))
m... the question is why left join? IMHO not needed.
I don’t know how it is in your Laravel, in SQL I think something like this:

SELECT t.value, count(*) as users_count
  FROM users u
  JOIN (
    SELECT d1.text as value, d1.user_id FROM data1 d1
    UNION
    SELECT d2.name as value, d2.user_id FROM data2 d2
  ) t on t.user_id = u.id
  GROUP BY t.value
  ORDER BY users_count DESC

PS: if you need to count AND matching records in data1 and data2, then replace UNION with UNION ALL
UPD: just do a raw select