count in more than one table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » count in more than one table

count in more than one table

Rate Topic

Display Mode

Topic Options

Author

Message

Thorsten Wittekopf

Posted Wednesday, February 06, 2013 6:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 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

Ed Wagner

Posted Wednesday, February 06, 2013 7:05 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 1,881, Visits: 553

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

Post #1416495

Thorsten Wittekopf

Posted Wednesday, February 06, 2013 10:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 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

gard68

Posted Wednesday, February 13, 2013 10:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 10:17 AM
Points: 1, Visits: 9

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

IgorMi

Posted Saturday, February 16, 2013 2:19 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 1,882, Visits: 1,459

Thorsten Wittekopf (2/6/2013)

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

Post #1420930

« Prev Topic | Next Topic »

Permissions