COUNT(*) of view1 UNION view2 giving strange results 1

[link9]

Hello all--

I have two views.

(1)vGulfStatesWeighted
(2)vSoutheastWeighted

SELECT count(*) FROM vGulfStatesWeighted
yields 1000 records

SELECT count(*) FROM vSoutheastWeighted
yields 1000 records

What I need now is to put these two views back on top of each other using a UNION query, which I would like to store in yet another view.

Problem is that when I issue this statement:

select * from vGulfStatesWeighted
union
select * from vsoutheastWeighted

I only get 1942 rows... I'm baffled. How could this possibly be true?

Each view has a unique column to identify it ('theRegion') once it's back in the new view that brings them together, which the syntax for is specified above (the UNION query), and then if I issue this command on the new view:

select count(*), theRegion from vMasterWeighted where theRegion = 5 or theRegion = 6
group by theRegion

I get this:

971 5
971 6

Can anyone explain this behavior to me? Or better yet, tell me how to get it to act right?

Thank you,
Paul Prewett

 

[fluteplr]

By default a union will remove duplicate rows.

From SQL BOL

UNION

Specifies that multiple result sets are to be combined and returned as a single result set.

ALL

Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

end BOL

Try it with a union all. And then do a query for regions other than 5 or 6.

 

[link9]

Great.

Thanks for the info, fluteplr

Worked a charm.

Paul Prewett