Join three quieries in one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join three quieries in one

Author

Topic

margo_qu
Starting Member

8 Posts

Posted - 2008-01-03 : 18:28:15

Hi guys,

I am a complete beginner and need your help. I have to display three queries, all from one table as three columns on the report:

1. Total number of different taxid
select count( distinct taxid) from provider;

2. Total number of taxid that don't have 9 digit in it
select count(taxid) from provider
where LENGTH(taxid)<9

3. Total number of records without taxid
select count(taxid) from provider
where taxid is null

I was doing a self-join, but it returns all three columns as zeros, which is incorrect.

margo_qu
Starting Member

8 Posts

Posted - 2008-01-03 : 19:04:20

Ok, Tom from Oracle.com answered my question. It can be done by using inline views or Case statement.

select a.n1, b.n2, c.n3
from
( select count ( distinct taxid ) as n1 from provider ) a ,
( select count ( * ) as n2 from provider
where length ( taxid ) < 9 ) b ,
( select count ( * ) as n3 from provider
where taxid is null ) c

Thank you ALL!

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-01-03 : 19:05:01

Yes, the case:


declare @Provider table (ProviderID int identity(1,1), TaxID varchar(10) null)
insert into @Provider (TaxID)
	select '1235467890' union all
	select '123456789' union all
	select '12345678' union all
	select null union all
	select null

select	count(*) [Total],
	sum(case when len(TaxID) < 9 then 1 else 0 end) [NotNine],
	sum(case when TaxID is null then 1 else 0 end) [NullTaxID]
from @Provider

Nathan Skerl

margo_qu
Starting Member

8 Posts

Posted - 2008-01-07 : 11:09:33

Nathan,

this is very expensive way to to it. It is better to use case statement:

SELECT
COUNT(DISTINCT TaxId) n1,
sum(CASE
WHEN LENGTH(TaxID) < 9
THEN 1 else 0 end) n2,
sum(case when taxid is null then 1 else 0 end) n3
FROM provider

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-01-07 : 12:55:09

quote:
Nathan, this is very expensive way to to it. It is better to use case statement

???

Nathan Skerl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-07 : 13:06:28

??? indeed

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources