?
Solved

Cross reference categories will performing a count

Posted on 2003-11-20
5
Medium Priority
?
225 Views
Last Modified: 2010-07-27
Hello all,

I have two tables.  One is a list of Categories, the other is a list of items with those categories assigned to each item.  There is a one to many relationship between the two tables.

tblCategories
---------------------
catName
catID

tblItems
---------------
ItemID
ItemName
catID

I need to count the number of items in tblItems for each category that is contained in tblCategories.  Moreover, I need to retrieve this data so that I can display it on a webpage, so I need to return, the catID and the Count of items with that catID.  I want to use a stored procedure.

---Desired Output------
catID     Count
1              22
2              109
3              67
etc. . .
--------------------------


Can someone please help me?
0
Comment
Question by:knottydrd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:morpheus30
ID: 9792136
CREATE PROCEDURE sp_CatItemCount AS

SELECT CatName, COUNT(ItemID) AS CountOfItem
FROM tblCategories C
INNER JOIN tblItems I ON C.CatID = I.CatID
GROUP BY CatName
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9792207
oopss...  You wanted CatID not CatName

CREATE PROCEDURE sp_CatItemCount AS

SELECT C.CatID, COUNT(ItemID) AS CountOfItem
FROM tblCategories C
INNER JOIN tblItems I ON C.CatID = I.CatID
GROUP BY CatName

Then on your web page you could do this...

<%

Dim oCn
Dim rs
Dim sql

sql = "sp_CatItemCount"

Set oCn = Server.CreateObject("ADODB.Connection")
oCn.Open "DSN=DSNName; uid=username; pwd=password"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, oCn, 1, 1, adCmdStoredProc

%>

<table>
<tr>
<td><%= rs(0).Name %></td><td><%= rs(1).Name %></td>
</tr>

<%
Do until rs.eof
%>

<tr>

<%
For x = 0 to rs.Fields.Count - 1
%>

<td><%= rs(x) %></td>

<%
Next
%>

</tr>

<%
rs.MoveNext
Loop
%>

</table>

rs.Close
Set rs = Nothing





0
 
LVL 5

Accepted Solution

by:
morpheus30 earned 2000 total points
ID: 9792236
oops, my bad on the above SQL Statement...
Forgot to group by C.CatID not CatName

SELECT C.CatID, COUNT(ItemID) AS CountOfItem
FROM tblCategories C
INNER JOIN tblItems I ON C.CatID = I.CatID
GROUP BY C.CatID
0
 

Author Comment

by:knottydrd
ID: 9792778
Dude, you are my freakin' hero.

That worked great, thanks.
0
 

Author Comment

by:knottydrd
ID: 9792832
Got time for one more question?
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question