Combine results of two queries postgres

Question

I have two queries like this:

SELECT project_id, user_ip, count(*) AS Count 
FROM "user" 
WHERE user_ip IS NOT NULL 
GROUP BY user_ip, project_id;

SELECT project_id, user_id, count(*) AS Count 
FROM "user" 
WHERE user_id IS NOT NULL 
GROUP BY user_id, project_id;

They are all the same except that one returns the users based on IP and other on ID.

Users can have only one of this, so basically if the user_ip is Null then user_id will have value and the opposite if user_id is Null then user_ip will have value.

So all I want is to make those query one query.

The first query gives this:

project_id | user_ip | Count            
  1        | 1.2.3.4 | 40        
  2        | 1.2.3.5 | 25    
  3        | 1.2.3.6 | 9          
  4        | 1.2.3.7 | 7 

The second gives this:

project_id | user_id | Count            
  1        |  1234   | 100        
  2        |  4567   | 50    
  3        |  4321   | 49  

So I want to make only one query that will return this:

project_id | user_id | user_ip | Count            
  1        |  1234   |         | 100 
  1        |         | 1.2.3.4 | 40       
  2        |  4567   |         | 50
  2        |         | 1.2.3.5 | 25     
  3        |  4321   |         | 49 
  3        |         | 1.2.3.6 | 9          
  4        |         | 1.2.3.7 | 7

I tried to do a left join also I tried to do it with Union but I get ERROR: UNION types text and integer cannot be matched

SELECT project_id, user_ip, count(*) AS Count 
FROM "user" 
WHERE user_ip IS NOT NULL 
GROUP BY user_ip, project_id  
UNION 
SELECT project_id, user_id, count(*) AS Count 
FROM "user" 
WHERE user_id IS NOT NULL GROUP 
BY user_id, project_id;

Can someone help me to achieve the results that I want? What can I use?

Answer 1

Add NULL as user_id/ip in the projection, in the SELECT clause.

SELECT project_id, NULL as user_id, user_ip, count(*) AS Count ...
union
SELECT project_id, user_id, NULL as user_ip, count(*) AS Count 

---

Alternatively, instead union, try group by project_id, user_id, user_ip, that should work too. This might be even faster.

SELECT project_id, user_id, user_ip, count(*) AS Count 
FROM "user" 
GROUP BY project_id, user_id, user_ip;