SQL Query – Count of Contacts By Record Type

I had a customer who wanted to be able to determine how many Prospects, Customers, Leads, etc they had in GoldMine. They keep track of their customers in a single database and use the KEY1 field to classify them as Prospects, Customers, Leads, etc. etc.

The best way to do this is via a SQL Query using a UNION operator. **

The following is an example of such a Query.

Select count(*),(CAST(‘Total Records‘ as varchar(80))) from contact1
UNION
Select count(*),(CAST(‘Total Prospects‘ as varchar(80)))from contact1 where key1=’Prospect’
UNION
Select count(*),(CAST(‘Total Leads‘ as varchar(80)))from contact1 where key1=’Lead’
UNION
Select count(*),(CAST(‘Total Customers‘ as varchar(80))) from contact1 where key1 = ‘Customer’

** The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.