Reusing a subquery

[TipTop]

Hi,

I have a (fairly) complicated query that I'm piecing together. There's
just one part of it that I can't seem to get my head around - maybe it's
not possible.

Here's an example I've made up to simplify my question (rather than
confuse things with the parts that already work). Essentially, I want to
get a list of all the SalesReps who have made sales in a certain
SalesRegion. But... accompanying each row I want to have a count of the
number of NewClient sales they have made - not just for the one region
but for all regions.

SELECT SalesRep, NewSales 'need to sum this somehow
FROM
(SELECT SalesRep,SalesRegion, SUM(IIF(IsNewClient,1,0)) as NewSales
FROM MySales
WHERE Sales.Date = #01/01/2005#
GROUP BY SalesRep, SalesRegion)
WHERE
SalesRegion = 1

The subquery will return something like:
name region totalnewsales
DAVE, 1 8
DAVE, 2 4
DAVE, 3 6
CHRIS 2 3
SUE 1 9
SUE 2 5
etc.

And I want the final result to be thus

DAVE, 18
SUE 14

i.e. only the people who have sales in region 1 but with the count of
new sales for all regions.

How do I get the the totals? Sure I could just query the database twice
for the required date but that seems a little clumsy to have it scan the
full table twice. Is there a way that I can use the subquery in one
query twice?

Thanks for any help,

Tiptop

 

[John Spencer (MVP)]

I think you need to change your subquery and use it in a where clause. Your
subquery should be identifying the sales reps in the sales region you are
interested in.

Perhaps the following or something similar would work for you.


SELECT SalesRep, Abs(Sum(IsNewClient)) as AllNewSales
FROM MySales
WHERE SalesRep in
(SELECT T.SalesRep
FROM MySales as T
WHERE T.SalesRegion = 1)
AND MySales.[Date] = #1/1/2005#
GROUP BY SalesRep

 

[TipTop]

That works very well indeed thank you very much!

A follow up question if I may:

I didn't manage to fathom this out myself because I was fixated with
having the check on the MySales.SalesDate in the subquery (at least
that's my excuse). I thought that running the query as below meant it
would be going through the entire table for *every* date checking each
record for its Region and SalesRep and therefore would be slow (it seems
pretty fast to me I must say) - therefore I went with trying to reduce
the number of records to be checked by the maximum I could by
"filtering" by date first. Is SQL/Access smarter than that?

Indeed, looking again at it, I guess it is quicker 'cause it has to run
through the full table anyway to look at the date so your way is to
reduce what it's looking at when it's doing the date comparison by
filtering by Region first and, since both SalesRegion and SalesRep are
actually IDs and indexed, means it's probably quicker your way anyway
(particularly since my way doesn't achieve what I want...). If that
makes sense at all...

Thanks again John, much appreciated!

Tiptop

I think you need to change your subquery and use it in a where clause. Your
subquery should be identifying the sales reps in the sales region you are
interested in.

Perhaps the following or something similar would work for you.


SELECT SalesRep, Abs(Sum(IsNewClient)) as AllNewSales
FROM MySales
WHERE SalesRep in
(SELECT T.SalesRep
FROM MySales as T
WHERE T.SalesRegion = 1)
AND MySales.[Date] = #1/1/2005#
GROUP BY SalesRep

Hi,

I have a (fairly) complicated query that I'm piecing together. There's
just one part of it that I can't seem to get my head around - maybe it's
not possible.

Here's an example I've made up to simplify my question (rather than
confuse things with the parts that already work). Essentially, I want to
get a list of all the SalesReps who have made sales in a certain
SalesRegion. But... accompanying each row I want to have a count of the
number of NewClient sales they have made - not just for the one region
but for all regions.

SELECT SalesRep, NewSales 'need to sum this somehow
FROM
(SELECT SalesRep,SalesRegion, SUM(IIF(IsNewClient,1,0)) as NewSales
FROM MySales
WHERE Sales.Date = #01/01/2005#
GROUP BY SalesRep, SalesRegion)
WHERE
SalesRegion = 1

The subquery will return something like:
name region totalnewsales
DAVE, 1 8
DAVE, 2 4
DAVE, 3 6
CHRIS 2 3
SUE 1 9
SUE 2 5
etc.

And I want the final result to be thus

DAVE, 18
SUE 14

i.e. only the people who have sales in region 1 but with the count of
new sales for all regions.

How do I get the the totals? Sure I could just query the database twice
for the required date but that seems a little clumsy to have it scan the
full table twice. Is there a way that I can use the subquery in one
query twice?

Thanks for any help,

Tiptop

 

[TipTop]

As an update:

FWIW, I had already written a query which ran two subqueries (the first
checking the date for all regions and returning the SUM and the second
checking the date for one region and returning the SalesReps).

I tried a speed comparison: repeating the query for each one 1000 times
(having inserted 10,000 dummy records into the table):
New: 00:01:19
Old: 00:00:37

So it appears checking the Sales table by date twice is quicker than
extracting all the regions for all dates and THEN filtering by
territory. Although the latter is a lot cleaner and saves me using a
LEFT JOIN.

Just an FYI - thanks again John for taking the time to answer my post in
the first place.

That works very well indeed thank you very much!

A follow up question if I may:

I didn't manage to fathom this out myself because I was fixated with
having the check on the MySales.SalesDate in the subquery (at least
that's my excuse). I thought that running the query as below meant it
would be going through the entire table for *every* date checking each
record for its Region and SalesRep and therefore would be slow (it seems
pretty fast to me I must say) - therefore I went with trying to reduce
the number of records to be checked by the maximum I could by
"filtering" by date first. Is SQL/Access smarter than that?

Indeed, looking again at it, I guess it is quicker 'cause it has to run
through the full table anyway to look at the date so your way is to
reduce what it's looking at when it's doing the date comparison by
filtering by Region first and, since both SalesRegion and SalesRep are
actually IDs and indexed, means it's probably quicker your way anyway
(particularly since my way doesn't achieve what I want...). If that
makes sense at all...

Thanks again John, much appreciated!

Tiptop

I think you need to change your subquery and use it in a where
clause. Your
subquery should be identifying the sales reps in the sales region you are
interested in.
Perhaps the following or something similar would work for you.


SELECT SalesRep, Abs(Sum(IsNewClient)) as AllNewSales
FROM MySales
WHERE SalesRep in (SELECT T.SalesRep FROM MySales as T
WHERE T.SalesRegion = 1)
AND MySales.[Date] = #1/1/2005#
GROUP BY SalesRep

Hi,

I have a (fairly) complicated query that I'm piecing together. There's
just one part of it that I can't seem to get my head around - maybe it's
not possible.

Here's an example I've made up to simplify my question (rather than
confuse things with the parts that already work). Essentially, I want to
get a list of all the SalesReps who have made sales in a certain
SalesRegion. But... accompanying each row I want to have a count of the
number of NewClient sales they have made - not just for the one region
but for all regions.

SELECT SalesRep, NewSales 'need to sum this somehow
FROM
(SELECT SalesRep,SalesRegion, SUM(IIF(IsNewClient,1,0)) as NewSales
FROM MySales
WHERE Sales.Date = #01/01/2005#
GROUP BY SalesRep, SalesRegion)
WHERE
SalesRegion = 1

The subquery will return something like:
name region totalnewsales
DAVE, 1 8
DAVE, 2 4
DAVE, 3 6
CHRIS 2 3
SUE 1 9
SUE 2 5
etc.

And I want the final result to be thus

DAVE, 18
SUE 14

i.e. only the people who have sales in region 1 but with the count of
new sales for all regions.

How do I get the the totals? Sure I could just query the database twice
for the required date but that seems a little clumsy to have it scan the
full table twice. Is there a way that I can use the subquery in one
query twice?

Thanks for any help,

Tiptop