SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


count in more than one table


count in more than one table

Author
Message
Thorsten Wittekopf
Thorsten Wittekopf
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
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
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16622 Visits: 10066
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
Thorsten Wittekopf
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
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
gard68
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
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
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5898 Visits: 5080
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search