| Author |
 Topic  |
|
|
Pace
Constraint Violating Yak Guru
United Kingdom
262 Posts |
 Posted - 12/12/2006 : 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"  |
Edited by - Pace on 12/12/2006 06:08:27
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/12/2006 : 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
United Kingdom
262 Posts |
Posted - 12/12/2006 : 06:08:02
|
Peso, you are truly an Animal! [:-p]
Thank you sir!
"Impossible is Nothing" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/12/2006 : 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
India
22461 Posts |
Posted - 12/12/2006 : 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
Sweden
29138 Posts |
Posted - 12/12/2006 : 07:52:20
|
Shouldn't the simple difference be calculated in front-end application? 
Peter Larsson
Helsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/12/2006 : 11:22:22
|
quote:
Originally posted by Peso
Shouldn't the simple difference be calculated in front-end application?
Peter Larsson
Helsingborg, Sweden
Yes
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
[SOLVED ]Help With Correlated Subquery
