SQL COUNT and DISTINCT

[mozingod]

Is there a way to get a DISTINCT row and a count of the columns in one Recordset? Such as:

strSQL = "SELECT DISTINCT Inspections, COUNT(*) FROM Mills WHERE PartNum = '" & Request.Form("PartNum&quot & "' GROUP BY Inspections"

?? It executes fine, but the script keeps timing out. I'm trying to loop through another record set that holds all the part info (called Rs), and in each loop iteration, loop though this record set (called Rs_Distinct) to determine if there's more then one Inspection number for each part number, and if there is, just display it once.

At the top of the page I have the following code, which works just fine:

Do While Not Rs_Distinct.EOF
If Rs_Distinct(1) > 1 Then
Response.Write("Assembly #" & Rs_Distinct(0) & " has multiple records.&quot
End If
Rs_Distinct.MoveNext()
Loop

After that code I move to the begining of Rs_Distinct and continue on...

Then I have bPrint, and in each iteration of Rs (Do While Not Rs.EOF), I loop through Rs_Distinct, and if Rs(1) > 1, I set bPrint = False and do Rs_Distinct.MoveLast(), otherwise bPrint stays True and I move to the next record in Rs_Distinct.

So I guess I'm asking if anyone happens to know why the script is timing out? Any help is much appreciated!! Darrell Mozingo

 

[mwolf00]

I recommend that you join the two queries into one rather than looping through one query and executing hundreds/thousands of secondary queries based on the results...

post your code, and I can optimize..... -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[mozingod]

Here it is (minus formatting and unnneeded info):

<%
Set Db = Server.CreateObject(&quot;ADODB.Connection&quot;)
	Db.ConnectionTimeOut = 15
	Db.Open &quot;...connection info..&quot;

strSQL = &quot;SELECT DISTINCT InspectionNum, COUNT(InspectionNum) FROM &quot; &_
    &quot;SalesHistory WHERE PartNum LIKE '%&quot; & Request.Form(&quot;PartNumber&quot;) & &quot;%' &quot; &_
    &quot;GROUP BY InspectionNum&quot;
	
Set Rs_Distinct = Server.CreateObject(&quot;ADODB.Recordset&quot;)
	Rs_Distinct.Open strSQL, Db, 1, 1

strSQL = &quot;SELECT * FROM SalesHistory WHERE SalesHistory.PartNum &quot; &_
    &quot;LIKE '%&quot; & Request.Form(&quot;PartNumber&quot;) & &quot;%'&quot;

Set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
	Rs.Open strSQL, Db, 1, 1

Do While Not Rs_Distinct.EOF		
    If Rs_Distinct(1) > 1 Then
        Response.Write(&quot;<br>#&quot; & Rs_Distinct(0) & &quot; - multiple records.&quot;)
    End If
		
    Rs_Distinct.MoveNext()
Loop

Response.Write(&quot;<table>&quot;)
Response.Write(&quot;<tr>&quot;)
Response.Write(&quot;<td><u>Part Number<u></td>&quot;)
Response.Write(&quot;<td><u>Inspection Number<u></td>&quot;)
Response.Write(&quot;</tr>&quot;)

Do While Not Rs.EOF	
	Rs_Distinct.MoveFirst()

	If Rs_Distinct.EOF And Rs_Distinct.BOF Then
		bPrint = True
	Else
		Do While Not Rs_Distinct.EOF
			If Rs_Distinct(0) = Rs(&quot;InspectionNum&quot;) And Rs_Distinct(1) > 1 Then
				bPrint = False
				Rs_Distinct.MoveLast()
			Else
				bPrint = True
				Rs_Distinct.MoveNext()
			End If	
		Loop
	End If
		
  If bPrint Then
	If Rs(&quot;PartNum&quot;) <> &quot;&quot; Then
		PartNum = Rs(&quot;PartNum&quot;)
	Else
		PartNum = &quot;-&quot;
	End If
		
	If Rs(&quot;InspectionNum&quot;) <> &quot;&quot; Then
		InspectionNum = Rs(&quot;InspectionNum&quot;)
	Else
		InspectionNum = &quot;-&quot;
	End If

	Response.Write(&quot;<tr>&quot;)
	Response.Write(&quot;<td>&quot; & PartNum & &quot;</td>&quot;)
	Response.Write(&quot;<td>&quot; & InspectionNum & &quot;</td>&quot;)
    Response.Write(&quot;</tr>&quot;)
    Rs.MoveNext()
Loop

Response.Write(&quot;</table>&quot;)
%>

Thanks for any help. Darrell Mozingo

 

[mozingod]

BTW, this is only for a handful of users on our Intranet, and there's only 5000 records in the database, with maybe 200 tops for any of these quieres, so performance isn't really that big to me, just so long as it works.... Thanks. Darrell Mozingo

 

[mwolf00]

If you use a query like:

"SELECT partNum, inspectionNum, COUNT(inspectionNum) "&_
"FROM salesHistory "&_
"WHERE partNum LIKE '%" & Request.Form("PartNumber&quot & "%' " &_
"GROUP BY partNum, inspectionNum"

Then you can get a list of all relevant parts. You can add " ORDER BY count(inspectionNum) " if you want to. Of you just want parts with multiple orders then you can add " HAVING count(inspectionNum) > 1".

Now you only need to loop though 1 recordset 1 time.... Also, you set "Db.ConnectionTimeOut = 15" which only gives the database 15 seconds to get your answer.... -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[mozingod]

That looks really good. I forgot about the HAVING clause How could I toss in there if I only wanted DISTINCT inspectionNum's though? Darrell Mozingo

 

[mwolf00]

When you use COUNT in this way (with GROUPING), the DISTINCT keyword is redundant and unnecesary. If you want you count solely by inspectionNum (regardless of partNum) then remove partNum from you SELECT and your GROUP BY clauses....

ie...
myTable
inspectionNum partNum
1 1
1 1
1 2
1 3
2 1
2 2
2 2
2 3


"SELECT partNum, inspectionNum, COUNT(inspectionNum) "&_
"FROM myTable "&_
"WHERE partNum LIKE '%" & Request.Form("PartNumber&quot & "%' " &_
"GROUP BY partNum, inspectionNum"

RESULTS:
inspectionNum partNum count()
1 1 2
1 2 1
1 3 1
2 1 1
2 2 2
2 3 1


"SELECT inspectionNum, COUNT(inspectionNum) "&_
"FROM myTable "&_
"WHERE partNum LIKE '%" & Request.Form("PartNumber&quot & "%' " &_
"GROUP BY inspectionNum"

RESULTS:
inspectionNum count()
1 4
2 4


-----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[mozingod]

Ohhh, thanks much! Darrell Mozingo