<div>
Update UNION to UNION ALL. Otherwise data may not be correct.<br />

<pre><code id="code-20-37599018-1">select a, b, c, SUM(cars) AS cars, SUM(houses) AS houses
from
(
	select a, b, c, count(*) As cars, 0 as houses from table1
	group by a, b, c
	UNION ALL
	select a, b, c, 0 as cars, Count(*) AS  houses from table2
	group by a, b, c
) a
group by a, b, c</code></pre>
<br />
Raj
</div>


Update UNION to UNION ALL. Otherwise data may not be correct.


select a, b, c, SUM(cars) AS cars, SUM(houses) AS houses
from
(
	select a, b, c, count(*) As cars, 0 as houses from table1
	group by a, b, c
	UNION ALL
	select a, b, c, 0 as cars, Count(*) AS  houses from table2
	group by a, b, c
) a
group by a, b, c


Raj

<div>
<div class="content wysiwyg-content">
how can I union a statment like this &nbsp;to get a result like the bottom output<br />
<br />
select a, b, c, count(*) As cars from table1<br />
group by a<br />
UNION<br />
select a, b, c, Count(*) AS &nbsp;houses from table2<br />
group by a<br />
<br />
I like the output to look something &nbsp;like this<br />
<br />
a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c &nbsp; &nbsp; &nbsp; Cars &nbsp; &nbsp;Houses <br />
--- &nbsp; &nbsp; &nbsp; ---- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;--- &nbsp; &nbsp; ------ &nbsp; ----------<br />
john &nbsp; &nbsp;Brown &nbsp; &nbsp;car &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2<br />
Kim &nbsp; &nbsp; Joins &nbsp; &nbsp; &nbsp;car &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3
</div>
</div>


how can I union a statment like this  to get a result like the bottom output

select a, b, c, count(*) As cars from table1
group by a
UNION
select a, b, c, Count(*) AS  houses from table2
group by a

I like the output to look something  like this

a          b            c       Cars    Houses 
---       ----          ---     ------   ----------
john    Brown    car     2              2
Kim     Joins      car     3              3

T-SQL Union

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.