[SOLVED ]Help With Correlated Subquery

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

SQL Server 2000 Forums

Transact-SQL (2000)

[SOLVED ]Help With Correlated Subquery

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-12-12 : 04:25:12

Hi All,

Im just messing about really and have made this query which shows me all my customers that have never had a quote from us;


SELECT
		COUNT(*) AS NoQuotes
FROM
		Customer AS C
		WHERE NOT EXISTS
		(
		SELECT
			*
		FROM
			Quotes AS Q
		WHERE
			Q.[Cust Code] = C.Code
		)

What I want to do is in a single query, also obtain a COUNT(*) From my customers, then subtract the value of no quotes but, so I can see everything.... So I would end up with;

Customers CustomersWithoutQuote Difference
-----------------------------------------------
10 8 2

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 04:36:45

SELECT count(*) as [Customers], sum(case when q.[cust code] is null then 1 else 0 end) as [Customers Without Quote]
from (select distinct code from customers) c
left join (select distinct [cust code] from quotes) q on q.[cust code] = c.code

Peter Larsson
Helsingborg, Sweden

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-12-12 : 06:08:02

Peso, you are truly an Animal! [:-p]

Thank you sir!

"Impossible is Nothing"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-12 : 06:49:11

quote:
Originally posted by Pace

Peso, you are truly an Animal! [:-p]

Thank you sir!

"Impossible is Nothing"

What did you mean by Animal?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-12 : 06:53:46

And for differnec you can use

Select Customers,[Customers Without Quote],Customers-[Customers Without Quote] as Difference
from
(
SELECT count(*) as [Customers], sum(case when q.[cust code] is null then 1 else 0 end) as [Customers Without Quote] from (select distinct code from customers) c
left join (select distinct [cust code] from quotes) q on q.[cust code] = c.code
) T

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 07:52:20

Shouldn't the simple difference be calculated in front-end application?