Summing Count queries

[Barry Phillips]

I am working on a database for sales leads. I have two tables which hold
dfferent type leads (Dealers, Homeowners), I already have several queries set
for each to select leads for each territory by state and to count leads for
each of our sales territories. What I need to be able to do is add these
Count functions together to get total leads for each territor. See below

These are seperate queries
qry dealer lead terr 1: count(*)
qry home lead terr 1: Count(*)

I need to add the count totals to get a sum for the territory for a report.
Any direction would be a great help. Actually there are a total of 4 tables
which holds leads for each territory I will be quering from but I only
included these 2 to simplify.
I need to do this for 22 Sales Territories any direction of how to
accomplish would be a great help.

 

[MGFoster]

I am working on a database for sales leads. I have two tables which hold
dfferent type leads (Dealers, Homeowners), I already have several queries set
for each to select leads for each territory by state and to count leads for
each of our sales territories. What I need to be able to do is add these
Count functions together to get total leads for each territor. See below

These are seperate queries
qry dealer lead terr 1: count(*)
qry home lead terr 1: Count(*)

I need to add the count totals to get a sum for the territory for a report.
Any direction would be a great help. Actually there are a total of 4 tables
which holds leads for each territory I will be quering from but I only
included these 2 to simplify.
I need to do this for 22 Sales Territories any direction of how to
accomplish would be a great help.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It sounds like your data model is incorrect. I believe you should have
all the leads in one table. A column in that table would indicate the
type of lead. That way it'd be easier to make a query to get what you
want.

Data Model example:

CREATE TABLE LeadTypes (
LeadTypeID int IDENTITY(1,1) ,
[Description] varchar(50) not null PRIMARY KEY
)
CREATE UNIQUE INDEX idxLeadTypeID ON LeadTypes (LeadTypeID)

CREATE TABLE Contacts (
ContactID int IDENTITY(1,1) NOT NULL ,
LeadTypeID int not null REFERENCES LeadTypes ,
-- ... other columns - name, address, etc. ...
)

The query you want:

SELECT LT.[Description] As LeadType, Count(C.*) As LeadTypeCount
FROM Contacts AS C INNER JOIN LeadTypes AS LT
ON C.LeadTypeID = LT.LeadTypeID
WHERE ...
GROUP BY LT.[Description]

The result set would be like this:

LeadType LeadTypeCount
============== =============
Dealears 25
HomeOwners 105
.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQbjjwoechKqOuFEgEQKNtwCdFkVLnmTDP2K6UMba4c9Qae6HE74AoIrP
LHtIZUvldHpsGgko0Ydol6bU
=Vx/q
-----END PGP SIGNATURE-----