CODEWITHSUNDEEP
sqlpostgresqlaggregatepostgresql-9.1
user6145033
user6145033

Reputation:

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?

Upvotes: 4

Views: 4544

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

First, use union all. Second, convert the values to text ... one is a number and the other a string:

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

You can also write this using GROUPING SETS in Postgres 9.5+:

SELECT project_id, COALESCE(user_ip::text, user_id::text),
       count(*) AS Count 
FROM "user" 
WHERE user_ip IS NOT NULL 
GROUP BY GROUPING SETS ((project_id, user_ip), (project_id, user_id))

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Grouping sets (9.5+)

select *
from (
    select project_id, user_id, user_ip, count(*)
    from "user"
    group by grouping sets ((project_id, user_id), (project_id, user_ip))
) s
where user_id is not null or user_ip is not null

Upvotes: 0

dimm
dimm

Reputation: 1798

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;

Upvotes: 3

tata.leona
tata.leona

Reputation: 1058

Queries of union need to have same number and type of all columns. Try this:

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

Upvotes: 3

Related Questions