Help with simple code!!

[MRoberto]

have two tables I am trying to work with…… We’ll call them VOL and TOC, each has more info in them but I’m only going to list what I am looking for



VOL

VOLID VOLNAME

1 Finance

2 Admin

3 IT

4 HR

5 Building

6 Example

250 Public Safety







TOC

VOLID[\u]

1

1

1

3

4

5

5

5

26

36

77*

77*

77*

77*



The TOC table stores multiple instances of each type of VOLID, and I need to run a query once a month looking for the current count of each instance of a VOLID





*When I use this statement

SELECT COUNT(*) AS Expr1

FROM Toc

WHERE (VolumeId = 77)



I get a result table that looks like



TOC

Expr1

4







I need either a stored procedure, or just a query to run in query analyzer that will give me a new table called, lets say STATS



STATS

VOLNAME COUNT

Finance 5

Admin 8

IT 20



I have gotten this statement:

SELECT A.VolumeName , COUNT(B.VolumeId) AS Images
FROM Vol A, TOC b
WHERE (B.VolumeId = 77)
group by VolumeName


To give me a results panel that looks like

HR 4
FINANCE 4
IT 4
BUILDING 4

But it is returning the same count.

I think I need to do a loop from 2-254 (as the volume ID range), so that it returns the corresponding count for the corresponding VolumeID

It should look like

HR 130
FINANCE 854
IT 43
BUILDING 92

Any questions please ask
I am not a programmer!!!!

 

[mrdenny]

You need to tell SQL how to join the two tables together.

SELECT     A.VolumeName , COUNT(B.VolumeId) AS Images
FROM         Vol A, TOC b
WHERE     (B.VolumeId = 77)
  and A.VOLID = B.VOLID
group by VolumeName

It could also be written this way.

SELECT     A.VolumeName , COUNT(B.VolumeId) AS Images
FROM         Vol A
JOIN TOC b on A.VOLID = B.VOLID
WHERE     (B.VolumeId = 77)
  group by VolumeName

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com