Help retrieving # of records per group

**Rebelle** · Rebelle, April 13th, 2009 04:29 PM

Hi All,

With my current code the data displays as...how can I get the info in blue/bold (# of records) ?

Code:

PartNo. - Desc (# of eq - 3)
-Desc-EQ#
-Desc-EQ#
-Desc-EQ#
PartNo.1 - Desc1 (# of eq - 2)
-Desc1-EQ#
-Desc1-EQ#
ETC...

Code:

<%
iRecCount = rs.RecordCount
if iNumRecs <> -1 And iRecCount > iStartRec+iNumRecs-1 then
	iStopRec = iStartRec+iNumRecs-1
else
	iStopRec = iRecCount
end if	
%>

<%
Dim strLastFLName
%>

<TABLE border="3" width=900>
<col width=300><col width=50>

      <TR bgcolor="#6699CC">
<TD><B>Description</B></TD>
<TD><B>EQNumber</B></TD>
</TR>

</CENTER>

<%
strLastFLName = ""

	do while not rs.EOF
	if i > iStopRec then
		exit do
	end if

	strCurrentFLName = rs("PartNo")
	strCurrentGrp = rs("Desc")
	
	If strCurrentFLName<>strLastFLName Then	
	Response.Write("<tr bgcolor=#999999><td align=""left"" colwidth=""100"" colspan=""2""><font color=""white"">" & strCurrentFLName & " -  " & strCurrentGrp & "</font></td></tr>")
	
	End If
	strLastFLName = strCurrentFLName


	
	i = i + 1

%>

<%
IF i Mod 2 = 0 THEN 
%>
<TR bgcolor="#f0e68c">
<%
ELSE
%>
<TR bgcolor="#FFFFCC">
<%
END IF
%>

<TD align=left><%=rs("Desc")%> &nbsp;</TD>
<TD align=right><%=rs("EQNumber")%> &nbsp;</TD>
</TR>
	
<%	
rs.MoveNext
loop
%>

**Wolffy** · Wolffy, April 13th, 2009 05:40 PM

Since you want the subcount at the top of the list, I would just go and do this a 2 queries -- the first with the appropriate count() and GROUP BY, and the second to get your detail info.

Or, you could do the count() and GROUP BY query and UNION the results (with appropriate NULL columns as necessary) with the detail query. Add a dummy column with a value of 1 for the summary data and 2 for the detail data and then sort the whole thing as ORDER BY DESCRIPTION, DUMMY_COLUMN. Something like:

Code:

select description, count(eq_number) eq_count, 1 as dummy_column
from foo
group by description
union 
select description, eq_number, 2 as dummy_column
from foo
order by description, dummy_column

**Rebelle** · Rebelle, April 14th, 2009 03:16 PM

Wolf wolf Wolffffffy

Should I be doing this on my asp sql statement or on a view in sql2000?

Also, could I use count on partno. with a Distinct?

Thanks much!

**Wolffy** · Wolffy, April 14th, 2009 03:31 PM

My preference is for creating a View. I actually never use a query against the SQL Tables directly -- only through a view.

Also, could I use count on partno. with a Distinct?

Huh? You lost me. GROUP BY usually takes the place of Distinct in aggregation. If you need to add PartNo into the mix, just GROUP BY it as well.

**Rebelle** · Rebelle, April 14th, 2009 04:37 PM

Hey wolffy ... sorry i'm having a hard time with this one...

Ok, in sql2000 analyzer I'm getting the following error:

Code:

Server: Msg 205, Level 16, State 1, Line 1
All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.

Here is what I have:

Code:

SELECT     Description,EQNumber,COUNT(EQNumber) AS eq_count, 1 as dummy
FROM         dbo.tblShip
GROUP BY Description,EQNumber
union
select Description,EQNumber, 2 as dummy
FROM dbo.tblShip
ORDER BY Description, dummy

**Wolffy** · Wolffy, April 14th, 2009 04:40 PM

Just add the following to the second query in your UNION

Code:

SELECT Description, EQNumber, NULL, 2 as DUMMY

The NULL will take the place of eq_count so that the number of columns is equal in both queries

**Rebelle** · Rebelle, April 14th, 2009 04:49 PM

okies that took care of the error...but i'm still trying to understand....when i look at the results now, it looks like:

Code:

Description -- EQNumber -- eq_count -- dummy
Desc1            3235            1           1
Desc1            3253            NULL      2
Desc2            1113            1           1
Desc2            1155            1           1
Desc2            1156            NULL      2
Desc2            1535            NULL      2
ETC...

I'm confused...shouldn't it show 2 for Desc1 and 4 for Desc2?

**Wolffy** · Wolffy, April 14th, 2009 04:58 PM

I don't think you want to GROUP BY EQNumber in the first query in the union -- you just wanted the total number of EQNumbers for a given Description, right?

If you take EQNumber out of the first query, take the NULL out of the second select.

**Rebelle** · Rebelle, April 14th, 2009 05:04 PM

Yes...closer now....

but its off by 1 number...ex:

desc1 has 2 records but eq_count shows 1
desc2 has 4 records but eq_count shows 3
desc3 has 8 records but eq_count shows 7

so its skipping 1

...plzzz help

i just noticed that equipment#'s and the count#'s show in the same column (eq_count)...so that column data reads:

Code:

**Wolffy** · Wolffy, April 14th, 2009 05:13 PM

Well, that's interesting. Just add 1 to the Count then -- only kidding.

Is the query you posted the EXACT query you are testing with? If not, NULLs can cause interesting things to happen.

Here's the data I tested with

Code:

description eq_number
----------- -----------
AAA         10
AAA         20
AAA         30
AAA         40
BBB         10
BBB         20
BBB         30
CCC         10
CCC         50

And the results

Code:

description eq_count    dummy_column
----------- ----------- ------------
AAA         4           1
AAA         10          2
AAA         20          2
AAA         30          2
AAA         40          2
BBB         3           1
BBB         10          2
BBB         20          2
BBB         30          2
CCC         2           1
CCC         10          2
CCC         50          2

If you want to zip-up your test data, I can look at the problem on the hoof.

Could also try replacing UNION with UNION ALL in this case.

Thread: Help retrieving # of records per group

LinkBack

Thread Tools

Display

Help retrieving # of records per group

Similar Threads

Group by Problem

Report date range as years in option group

order by group by problem

Sql group by

Summing for each group only

Bookmarks

Bookmarks

Posting Permissions