Counting records in a query 1

[calista]

I have a table of IP addresses that includes the address, and the person's ID that address is assigned to. What I'm trying to get to is a query that puts out a list of owners and the total number of addresses assigned to each owner. Obviously, each address is in there only once, but any given owner will have many addresses. The following is what I have:

<!--- Query IP table and group by owner. --->
		<CFQUERY NAME=&quot;GetIPOwners&quot; 
			DATASOURCE=&quot;#Application.Datasource#&quot; 
			DBTYPE=&quot;ODBC&quot;>
				SELECT		IPA_ID,IPA_Address,IPA_PersonID,
							COUNT (IPA_PersonID) AS AdrCount
				FROM		IP_AdrTable
				WHERE		IPA_Available = No
				GROUP BY	IPA_PersonID,IPA_ID,IPA_Address
				ORDER BY	IPA_PersonID
		</CFQUERY>
		
		<BR><BR>
		<CFOUTPUT QUERY=&quot;GetIPOwners&quot; GROUP=&quot;IPA_PersonID&quot;>#IPA_PersonID#<BR>#AdrCount#</CFOUTPUT>
		<BR><BR>

What I get in return is the owner listed once, which is fine, but AdrCount returns 1, and it should return 10 from my test data. Any suggestions welcome! Thanks! Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

Try this?:

<!--- Query IP table and group by owner. --->
<CFQUERY NAME=&quot;GetIPOwners&quot; etc.>
SELECT IPA_ID,IPA_Address,IPA_PersonID,COUNT(IPA_Address) AS AdrCount
FROM IP_AdrTable
WHERE IPA_Available = No
AND IPA_PersonID =
(SELECT DISTINCT IPA_PersonID
FROM IP_AdrTable)
GROUP BY IPA_PersonID,IPA_ID,IPA_Address
ORDER BY IPA_PersonID
</CFQUERY>

John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Thanks, John. Sorry, same result. I even double-checked my test data to make sure what I was supposed to be getting, and I'm right. I have 10 addresses in the table, and they are all registered to one owner, me! It still returns my ID once, and AdrCount is still 1. I knew there was a reason I didn't like SQL. I actually have a similar query on my discussion board that shows the numbers of messages posted to each thread, and that one works fine. So, I don't know what the difference is. Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

Are you sure you changed your COUNT(IPA_PersonID) to my
COUNT(IPA_Address) AS AdrCount? John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Yes, in fact I cut-and-pasted the SQL directly from your post. Just for comparison, here's my query that works:

<CFQUERY NAME=&quot;GetThreads&quot; DATASOURCE=&quot;#Application.Datasource#&quot;>
	SELECT 	ThreadID,
			ThreadName, 
			ThreadDate,
			ThreadForumID,
			Count(MessageID) AS MessCount,
			Max(MsgDatePosted) as lastpost
	FROM 	ThreadTable, 
			MessageTable
	WHERE 	ThreadID = MsgThreadID
	AND		ThreadForumID = '#Forum#'
	GROUP BY 	ThreadForumID,
				ThreadID, 
				ThreadName,
				ThreadDate
</CFQUERY>

MessCount returns the correct number of messages posted to the thread. Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

Well then, it's time to parse the problem until we find the solution. Try eliminating the GROUP BY and the WHERE IP_Available = no clauses, so:

<CFQUERY NAME=&quot;GetIPOwners&quot; etc.>
SELECT IPA_ID,IPA_Address,IPA_PersonID,COUNT(IPA_Address) AS AdrCount
FROM IP_AdrTable
WHERE IPA_PersonID =
(SELECT DISTINCT IPA_PersonID
FROM IP_AdrTable)
</CFQUERY>

John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Here's the query I copied from you:

<CFQUERY NAME=&quot;GetIPOwners&quot; 
				DATASOURCE=&quot;#Application.Datasource#&quot; 
				DBTYPE=&quot;ODBC&quot;>
			    SELECT IPA_ID,IPA_Address,IPA_PersonID,COUNT(IPA_Address) AS AdrCount
			    FROM IP_AdrTable
			    WHERE IPA_PersonID = 
			      (SELECT DISTINCT IPA_PersonID 
			       FROM IP_AdrTable)
	   		</CFQUERY>

I got the follwing error:

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


[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'IPA_ID' as part of an aggregate function.


SQL = &quot;SELECT IPA_ID,IPA_Address,IPA_PersonID,COUNT(IPA_Address) AS AdrCount FROM IP_AdrTable WHERE IPA_PersonID = (SELECT DISTINCT IPA_PersonID FROM IP_AdrTable)&quot;

So, I put the &quot;GROUP BY&quot; clause back on, and the query runs, but I still get a count of 1.

Thanks for sticking with me!

Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

Actually, you might want to remove persin_id from the select clause,

<CFQUERY NAME=&quot;GetIPOwners&quot; etc.>
SELECT IPA_ID,IPA_Address,COUNT(IPA_Address) AS AdrCount
FROM IP_AdrTable
WHERE IPA_PersonID =
(SELECT DISTINCT IPA_PersonID
FROM IP_AdrTable)
</CFQUERY>

You could also write a query that selects distinct Person_ID (call it &quot;getPersonID&quot, then loop thru that query with the one above, slightly modified.

(i.e., &quot;WHERE Person_ID = #getPersonID.Person_ID#&quot; John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Still the same result, so, I think I'll take your other suggestion of selecting the IDs, and then querying the table. Thanks for your help! Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

You are right, the group bys have to stay. Just to get that number, how about:

<CFQUERY NAME=&quot;GetIPOwners&quot; etc.>
SELECT COUNT(IPA_Address) AS AdrCount
FROM IP_AdrTable
WHERE IPA_PersonID =
(SELECT DISTINCT IPA_PersonID
FROM IP_AdrTable)
AND IP_Available = NO
</CFQUERY>

Heck, write another query for the actual addresses if you want.


John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Well, I got a couple of errors, so I modified it to the following, and it actually works!

<!--- Query IP table and group by owner. --->
<CFQUERY NAME=&quot;GetIPOwners&quot; 
	DATASOURCE=&quot;#Application.Datasource#&quot; 
	DBTYPE=&quot;ODBC&quot;>
		SELECT IPA_PersonId,COUNT(IPA_Address) AS AdrCount
    	FROM IP_AdrTable
    	WHERE IPA_PersonID = 
      	(SELECT DISTINCT IPA_PersonID 
      	 FROM IP_AdrTable) 
   		 AND IPA_Available = NO
		 GROUP BY IPA_PersonID
</CFQUERY>

Thank you very much! Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

Yeah! Good job. John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Well, I thought I had it. As soon as I released a couple of those addresses, and IP_Available=&quot;Yes&quot;, and IPA_PersonID was blank, the query crashed. This really does work the way I want it to. The key was the line in red.
<CFQUERY NAME=&quot;GetIPOwners&quot; DATASOURCE=&quot;#Application.Datasource#&quot;>
SELECT IPA_PersonID,
Count(IPA_PersonID) AS AdrCount
FROM IP_AdrTable,PersonTable
WHERE IPA_Available = No
AND PersonID = IPA_PersonID
GROUP BY IPA_PersonID
</CFQUERY>

Now, on to bigger and better things! Calista :-X
Jedi Knight,
Champion of the Force