COUNT and UNION?
  Home FAQ Contact Sign in
microsoft.public.sqlserver.programming only
 
Advanced search
POPULAR GROUPS

more...

microsoft ... programming Profile…
 Up
COUNT and UNION?         


Author: Me!
Date: Jun 16, 2008 07:45

Hi,

I have the following very simple union query. I am union joining two select
distinct lists to get a unique list of values . Can anyone tell me how to
amend the query below so it just returns a COUNT of the rows returned. i.e
my query returns 19,459 rows, so I want it to return just one value:-
19,459.

Many thanks.....Jason
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_Used

UNION

SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_New
5 Comments
Re: COUNT and UNION?         


Author: Alex Kuznetsov
Date: Jun 16, 2008 07:52

On Jun 16, 9:45 am, "Me!" wrote:
> Hi,
>
> I have the following very simple union query. I am union joining two select
> distinct lists to get a unique list of values . Can anyone tell me how to
> amend the query below so it just returns a COUNT of the rows returned. i.e
> my query returns 19,459 rows, so I want it to return just one value:-
> 19,459.
>
> Many thanks.....Jason
> SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_Used
>
> UNION
>
> SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_New

SELECT COUNT(*) FROM(
SELECT fr_ID FROM dbo.DEADCAR_Monitor_Used

UNION
Show full article (0.78Kb)
no comments
Re: COUNT and UNION?         


Author: vinu
Date: Jun 16, 2008 07:51

HI

select count(fr_ID) TotalCount
from(
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_Used
UNION
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_New
) rs

vinu
Show full article (0.75Kb)
no comments
Re: COUNT and UNION?         


Author: Tibor Karaszi
Date: Jun 16, 2008 07:50

One option:

SElECT COUNT(*)
FROM
(
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_Used
UNION
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_New
) AS x

Or, if 2005 you can also use a derived table:

WITH x AS
(SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_Used
UNION
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_New
)
SElECT COUNT(*)
FROM x
Show full article (1.06Kb)
no comments
Re: COUNT and UNION?         


Author: TheSQLGuru
Date: Jun 16, 2008 09:04

A minor point to the OP: If your two tables cannot possibly have duplicates
you should use UNION ALL, which will avoid the overhead of sorting and
distincting the union set:

SELECT COUNT(*)
FROM
(
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_Used
UNION ALL
SELECT DISTINCT fr_ID FROM dbo.DEADCAR_Monitor_New
) AS x

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"Tibor Karaszi" hotmail.nomail.com> wrote in
message news:%%23hgyiB8zIHA.3680@TK2MSFTNGP05.phx.gbl...
> One option:
>
> SElECT COUNT(*)
> FROM
> (
> SELECT DISTINCT fr_ID...
Show full article (1.69Kb)
no comments
Re: COUNT and UNION?         


Author: Me!
Date: Jun 17, 2008 10:46

Thanks everyone for the replies. I forgot UNION (without the all)
eliminated the duplicate records so my DISTINCTs were redundant.

All your help is appreciated everyone,....Jason
Show full article (0.61Kb)
no comments

RELATED THREADS
SubjectArticles qty Group
US-NJ: Union-Pharmaceutical Sales Representative - Union NJ1 alt.bestjobsusa.jobs.offered ·