Select count issue

I am having a problem with counting a distinct set of people from mulitiple tables. I have the following query.

<cfquery name="crossmatch" datasource="datasource">
select count(*) as doctortotal
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
</cfquery>


I am counting the number of people from three different tables. However, I also have another query with everything the same except I have
"select distinct u.FIRST_NAME, u.LAST_NAME"
so that I can get a list of the people's names as well. The problem is that these numbers don't match up, because there are some duplicates of the same people in the thrid table. I don't want to go in and delete these people from the table, because there are like 60,000 people in one table. So I need to be able to do the following which I know doesn't work because of the syntax, but this is what I am trying to do. I know that count only takes one arg.
"select count(distinct u.FIRST_NAME, u.LAST_NAME) "

any suggestions on how to do this?
Thanks

Use DISTINCT? But in that case you'll need something that is distinct to count on...an ID? Combination of first and last name, etc.

How is it done with a combination of first and last name, because that is what I want, and everything I try gives me an error

Well it seems that COUNT only take one argument, so you can't give it multiple fields. What you'll have to do is create an inner query that has only the distinct first and last names from the tables you are joining, and then run an outer query that does "select count(*)..." on that inner result set.

I get an error stating:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 16: Incorrect syntax near ')'.

Here is my query. Is this not the way to do it.

Thanks for all of your help kiteless.



<cfquery name="crossmatch" datasource="datasource">
select count(*) as doctortotal
from (select distinct u.FIRST_NAME, u.LAST_NAME
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
)
</cfquery>

The first test would be, does just running the inner query give you a list of all the distinct doctor names?

select distinct u.FIRST_NAME, u.LAST_NAME
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE

yes it does, I have that setup fine, which is when i found out that I had an difference of numbers between just doing the count and just taking the distinct names. I found that some of the people in the database had been duplicated. So now I want to count those distinct people.

And this creates an error (simply wrapping it with a count query)?

select count(*) as total from (
select distinct u.FIRST_NAME, u.LAST_NAME
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
)

yes the error which I posted above which is

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 18: Incorrect syntax near ')'.

Very odd....what about aliasing the final result?

select count(x.*) as total from (select distinct u.FIRST_NAME, u.LAST_NAME
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE) x

No that didn't work I was given an error stating:
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '*'.

I know this has to be possible some way. So here is my whole query: If you take out the x's and the inner query that we added(from(select distinct u.FIRST_NAME, u.LAST_NAME) it works fine expect like I was saying before it still includes all of the people who were duplicates

kiteless, Really appreciate your help

<cfquery name="crossmatch" datasource="datasource">
select count(x.*) as doctortotal
from (select distinct u.FIRST_NAME, u.LAST_NAME
<cfif form.list IS NOT "0" AND form.list1 IS "0" AND form.list2 IS "0" AND form.list3 IS "0" AND form.list4 IS "0" AND form.list5 IS "0">
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
<cfset Variables.thelist =1>
<cfset listname = "Xolair">
</cfif>
<cfif form.list IS NOT "0" AND form.list1 IS NOT "0" AND form.list2 IS "0" AND form.list3 IS "0" AND form.list4 IS "0" AND form.list5 IS "0">
from #form.list# t3,
#form.list1# t4,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
and t4.FIRST_NAME = u.FIRST_NAME
and t4.LAST_NAME = u.LAST_NAME
and t4.STATE = u.STATE
<cfset Variables.thelist =2>
</cfif>
<cfif form.list IS NOT "0" AND form.list1 IS NOT "0" AND form.list2 IS NOT "0" AND form.list3 IS "0" AND form.list4 IS "0" AND form.list5 IS "0">
from #form.list# t3,
#form.list1# t4,
#form.list2# t5,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
and t4.FIRST_NAME = u.FIRST_NAME
and t4.LAST_NAME = u.LAST_NAME
and t4.STATE = u.STATE
and t5.FIRST_NAME = u.FIRST_NAME
and t5.LAST_NAME = u.LAST_NAME
and t5.STATE = u.STATE
<cfset Variables.thelist =3>
</cfif>
<cfif form.list IS NOT "0" AND form.list1 IS NOT "0" AND form.list2 IS NOT "0" AND form.list3 IS NOT "0" AND form.list4 IS "0" AND form.list5 IS "0">
from #form.list# t3,
#form.list1# t4,
#form.list2# t5,
#form.list3# t6,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
and t4.FIRST_NAME = u.FIRST_NAME
and t4.LAST_NAME = u.LAST_NAME
and t4.STATE = u.STATE
and t5.FIRST_NAME = u.FIRST_NAME
and t5.LAST_NAME = u.LAST_NAME
and t5.STATE = u.STATE
and t6.FIRST_NAME = u.FIRST_NAME
and t6.LAST_NAME = u.LAST_NAME
and t6.STATE = u.STATE
<cfset Variables.thelist =4>
</cfif>
<cfif form.list IS NOT "0" AND form.list1 IS NOT "0" AND form.list2 IS NOT "0" AND form.list3 IS NOT "0" AND form.list4 IS NOT "0" AND form.list5 IS "0">
from #form.list# t3,
#form.list1# t4,
#form.list2# t5,
#form.list3# t6,
#form.list4# t7,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
and t4.FIRST_NAME = u.FIRST_NAME
and t4.LAST_NAME = u.LAST_NAME
and t4.STATE = u.STATE
and t5.FIRST_NAME = u.FIRST_NAME
and t5.LAST_NAME = u.LAST_NAME
and t5.STATE = u.STATE
and t6.FIRST_NAME = u.FIRST_NAME
and t6.LAST_NAME = u.LAST_NAME
and t6.STATE = u.STATE
and t7.FIRST_NAME = u.FIRST_NAME
and t7.LAST_NAME = u.LAST_NAME
and t7.STATE = u.STATE
<cfset Variables.thelist =5>
</cfif>
<cfif form.list IS NOT "0" AND form.list1 IS NOT "0" AND form.list2 IS NOT "0" AND form.list3 IS NOT "0" AND form.list4 IS NOT"0" AND form.list5 IS NOT "0">
from #form.list# t3,
#form.list1# t4,
#form.list2# t5,
#form.list3# t6,
#form.list4# t7,
#form.list5# t8,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
and t4.FIRST_NAME = u.FIRST_NAME
and t4.LAST_NAME = u.LAST_NAME
and t4.STATE = u.STATE
and t5.FIRST_NAME = u.FIRST_NAME
and t5.LAST_NAME = u.LAST_NAME
and t5.STATE = u.STATE
and t6.FIRST_NAME = u.FIRST_NAME
and t6.LAST_NAME = u.LAST_NAME
and t6.STATE = u.STATE
and t7.FIRST_NAME = u.FIRST_NAME
and t7.LAST_NAME = u.LAST_NAME
and t7.STATE = u.STATE
and t8.FIRST_NAME = u.FIRST_NAME
and t8.LAST_NAME = u.LAST_NAME
and t8.STATE = u.STATE
<cfset Variables.thelist =6>
</cfif>)x
</cfquery>

Yikes, that's a hell of a query. One more test, can your replace the variables with real values and run this query in the SQL server SQL editor?

select count(*) as total from (
select distinct u.FIRST_NAME, u.LAST_NAME
from #form.list# t3,
(select distinct t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select distinct t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
)

I get an error stating that
ADO error: Line 1 Incorrect syntax near ')', when I check the SQL syntax, however when I run it gives me the right thing, and then when I go back and look at the SQL this is what it has.
but I have no idea what the "DERIVEDTBL" table is

SELECT COUNT(*) AS total
FROM (SELECT DISTINCT u.FIRST_NAME, u.LAST_NAME
FROM Concerta t3,
(SELECT DISTINCT t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
FROM Verispan t1
UNION ALL
SELECT DISTINCT t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
FROM Accent t2) u
WHERE t3.FIRST_NAME = u.FIRST_NAME AND t3.LAST_NAME = u.LAST_NAME AND t3.STATE = u.STATE) DERIVEDTBL

Huh, then it's possible that SQL Server won't let you run aggregate functions on inline views, since it's giving you the ADO error. What about just running the inner query and then in your CF code using the recordcount to give you the count?

yea I think you are right. I don't want to show the names of everyone though, I just want to give the record count, so would I do that on the output query or what. Thanks again