How can I combine two postgres select statements?-CodePudding

I have two different select statements that I was using individually, both having that resource field in place.

SELECT count(*) AS totalNumber, resource FROM phone_auth GROUP BY resource

and

SELECT count(*) AS totalNumber, resource FROM computer_auth GROUP BY resource

Not I am trying to have the count sum from both tables. Is there a way to do so?

CodePudding user response：

here is one way :

you can union tables first then use group by roll up

select count(*) AS totalNumber, resource, gtype 
from (
SELECT 'phone' as gtype, resource FROM phone_auth 
union all
SELECT 'computer' as gtype, resource FROM computer_auth
) t group by rollup (resource , gtype)

CodePudding user response：

You can use a union like this

SELECT count(*) AS totalNumber, resource FROM(
  SELECT resource FROM phone_auth
  UNION
  SELECT resource FROM computer_auth
) AS merge GROUP BY resource