count in more than one table

Question

count in more than one table

Thorsten Wittekopf

Valued Member

Points: 62
More actions
February 6, 2013 at 6:04 am

#270493

Hi folks,
I do two tables that have the same structure. Table 1 (SalesNew) contains the sales for the actual year, table 2 (SalesOld) for the last year.
Now I want to count the sales for this year and last year.
select salesman.Name,Count(SalesOld.SalesManID)+Count(SalesNew.SalesManID) As NumberOfSales
from salesman
Left Outer Join SalesOld On SalesOld.SalesManID = salesman.SalesManID and SalesOld.SalesDate >='01.01.2012' and SalesOld.SalesDate <'01.01.2013'
Left Outer Join SalesNew On SalesNew.SalesManID = salesman.SalesManID and SalesNew.SalesDate >='01.01.2013' and SalesNew.SalesDate <'01.01.2014'
Having Count(SalesOld.SalesManID)+Count(SalesNew.SalesManID)>1000
does run within the SQL Management console but it runs till there comes up a time out message.
So here is my question:
How do I count the entries for one salesman in both tables within one select?
Many thanks in advance
Thorsten

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

There's several different avenues of approach here and I'm sure I won't see them all without having the DDL to play around with.

The first thing I notice is that a salesman may not have sales returned from both tables, which is why you're outer joining the tables together. If this happens, you'll get a NULL sum, so I'd change the query to include ISNULLs as follows:

SELECT salesman.Name, ISNULL(COUNT(SalesOld.SalesManID), 0) + ISNULL(COUNT(SalesNew.SalesManID), 0) NumberOfSales

FROM salesman

LEFT OUTER JOIN SalesOld ON SalesOld.SalesManID = salesman.SalesManID AND SalesOld.SalesDate >= '01.01.2012' AND SalesOld.SalesDate < '01.01.2013'

LEFT OUTER JOIN SalesNew ON SalesNew.SalesManID = salesman.SalesManID AND SalesNew.SalesDate >= '01.01.2013' AND SalesNew.SalesDate < '01.01.2014'

GROUP BY salesman.Name

HAVING ISNULL(COUNT(SalesOld.SalesManID), 0) + ISNULL(Count(SalesNew.SalesManID), 0) >1000;

The next thing I'd do is to make sure things are indexed. From names only, I presume salesman.SalesManID is the primary key and SalesOld.SalesManID and SalesNew.SalesManID are the foreign keys by design, so make sure they are defined as such. I'd then make sure nonclustered indexes are defined on SalesOld and SalesNew.

CREATE NONCLUSTERED INDEX SalesOld_IDX01 ON SalesOld(SalesManID, SalesDate);

CREATE NONCLUSTERED INDEX SalesNew_IDX01 ON SalesOld(SalesManID, SalesDate);

If you have other columns to compare or return from the salesman table, you'll want to look at creating a covering index there as well.

HTH

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Thorsten Wittekopf Valued Member Points: 62 More actions · Answer 2

Many thanks,

but the problem is that the result for NumberOfSales is Count(SalesOld.SalesManID)*Count(SalesNew.SalesManID)*2.

For example one salesman has 35 entries in SalesNew and 334 in SalesOld.

The result of the Count(SalesOld.SalesManID)+Count(SalesNew.SalesManID) is 35*334*2=23380

CU

Thorsten

gard68 SSC Rookie Points: 47 More actions · Answer 3

Hi folks,

here comes the solution:

select salesman.Name,Count(Sales.SalesManID) As NumberOfSales

from salesman, (select SalesManID, SalesDate FROM SalesOld UNION ALL select SalesManID, SalesDate FROM SalesNew) AS Sales

where Sales.SalesManID=salesman.SalesManID

group by salesman.Name

Having Count(Sales.SalesManID) >1000

CU

Thorsten

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Thorsten Wittekopf (2/6/2013)
Many thanks,
but the problem is that the result for NumberOfSales is Count(SalesOld.SalesManID)*Count(SalesNew.SalesManID)*2.
For example one salesman has 35 entries in SalesNew and 334 in SalesOld.
The result of the Count(SalesOld.SalesManID)+Count(SalesNew.SalesManID) is 35*334*2=23380
CU
Thorsten

Hi,

More ways, look these two:

1) Create a View with UNION ALL between the tables and then count it.

2) If your tables have clustered indexes then you can the following way fastest, under a condition you don't update the statistics with different rowcount.

select p.[rows] + (select p2.[rows] from sys.partitions p2 where p2.object_id=object_id('dbo.SalesOld') and p2.index_id=1)

from sys.partitions p where p.object_id=object_id('dbo.SalesNew') and p.index_id=1

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com