Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

count in more than one table Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 6:01 AM
Points: 2, Visits: 13
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
Post #1416455
Posted Wednesday, February 6, 2013 7:05 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,053, Visits: 3,490
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
Post #1416495
Posted Wednesday, February 6, 2013 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 6:01 AM
Points: 2, Visits: 13
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
Post #1416620
Posted Wednesday, February 13, 2013 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:59 AM
Points: 1, Visits: 12
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
Post #1419643
Posted Saturday, February 16, 2013 2:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 2,980, Visits: 3,013
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
www.seavus.com
Post #1420930
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse