How to join result of two sql statements into one table and different columns How to join result of two sql statements into one table and different columns sql sql

How to join result of two sql statements into one table and different columns


Just wrap each select statement in parentheses, give each select statement an alias, and use SELECT at the top:

SELECT   (select count(*) as cdr     from abc as c with (nolock)    where appid in(1,2)  and calldate = '2012-10-09'  ) AS Column1,    (select count(*) as success     from abc as d with (nolock)    where appid in(44,45) and calldate = '2012-10-09'        and hangupcode in ('man', 'mach')  ) AS Column2,   (select count(*) as fail     from abc  with (nolock)    where appid in(44,45) and calldate = '2012-10-09'        and hangupcode not in  ('man', 'mach')  ) AS Column3

Basically you're treating each query as an individual column.


  SELECT a.cdr, b.success, c.failure FROM   (SELECT count(*) AS cdr     FROM abc as c WITH (NOLOCK)    WHERE appid IN (1,2) AND         calldate = '2012-10-09'  ) AS a,     (SELECT count(*) AS success     FROM abc AS d WITH (NOLOCK)    WHERE appid IN (44,45) AND          calldate = '2012-10-09' AND          hangupcode IN ('man', 'mach')  ) AS b,    (SELECT count(*) AS fail     FROM abc WITH (NOLOCK)    WHERE appid IN (44,45) AND         calldate = '2012-10-09' AND          hangupcode NOT IN ('man', 'mach')  ) AS c


select a.cdr, b.success, c.fail from( select count(*) as cdr  from abc as c with (nolock) where appid in(1,2)  and calldate = '2012-10-09' ) a, ( select count(*) as success  from abc as d with (nolock) where appid in(44,45)  and calldate = '2012-10-09'and hangupcode in ('man', 'mach') ) b, ( select count(*) as fail  from abc  with (nolock) where appid in(44,45) and calldate = '2012-10-09'and hangupcode not in  ('man', 'mach') ) c