Question 261560 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to merge two SQL tables

I have one MySQL query that shows total users grouped by countries (t1).

I have another MySQL query which shows countries that only has inactive users (t2).

Now, I want to have a resulting table that shows all the countries, total # users in those countries, followed by # inactive users in those countries (zero if there are no inactive users in those countries).

total users = active users + inactive users

This is the code so far:

with t1 as
(
Select country, count(users.id) AS 'total_users'
from users
group by country
),

t2 as 
(
Select country, count(users.id) AS 'inactive_users' 
From users left join orders on users.id = orders.user_id 
Where orders.created_at is null
Group by Country)

How do I establish the resulting table ?

Tags:

2

Answers


  1. 0 
    SELECT users.country, 
       COUNT(DISTINCT users.id) AS total_users,
       SUM(orders.user_id IS NULL) inactive_users
FROM users
LEFT JOIN orders ON users.id = orders.user_id 
GROUP BY users.country
    Login or Signup to reply.
  2. 0 
       SELECT [country] = users.country, 
          [total_users] = COUNT(users.id),
          [inactive_users] = SUM(CASE WHEN orders.created_at IS NULL THEN 1 ELSE 0 END)
     FROM users 
LEFT JOIN orders ON users.id = orders.user_id 
 GROUP BY users.country;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search