CODEWITHSUNDEEP
sqlpostgresql
Lilac Liu
Lilac Liu

Reputation: 49

merge two results to one table

If I have

SELECT count(id) FROM students;

count
---------
123

SELECT count(id) FROM staff;

count
---------
456

I want it to be like following

nstu  | nsta
--------------
123   | 456

How can I do that?

Upvotes: 1

Views: 75

Answers (5)

Gaetan
Gaetan

Reputation: 66

Just use subquery

SELECT * FROM  
    (SELECT count(id) FROM students) as nstu ,
    (SELECT count(id) FROM staff) nsta;

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Do a cross join:

select s1.nstu, s2.nsta
from
(SELECT count(*) as nstu FROM students) s1
cross join
(SELECT count(*) as nsta FROM staff) s2

Upvotes: 1

WernerW
WernerW

Reputation: 812

Another approach using union instead of subquery (MSSQL)

SELECT SUM(Students) AS 'Students' ,SUM(Staff) AS 'Staff'
FROM
(
    SELECT 'Student' AS 'Type', count(id) AS 'Students',0 AS 'Staff' FROM students
    UNION ALL
    SELECT 'Staff' AS 'Type', 0 AS 'Students',count(id) AS 'Staff' FROM staff
) AS Subset

Upvotes: 0

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT (SELECT count(id) FROM students) as nstu ,(SELECT count(id) FROM staff) nsta;

For Oracle 

SELECT (SELECT count(id) FROM students) as nstu ,
       (SELECT count(id) FROM staff) nsta 
FROM Dual;

Upvotes: 1

SevincQurbanova
SevincQurbanova

Reputation: 366

Select
(SELECT count(id) FROM students) c1,
(SELECT count(id) FROM staff) c2
from dual;

Upvotes: 1

Related Questions