Egjupss Egjupss - 1 month ago 7
SQL Question

Combine results of two queries postgres

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

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;