Count number of times appearing in result set.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count number of times appearing in result set.

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 18:15:42

The #tempa that the below refers to is as such

LocID int
Locname varchar (100)
JobID int

and could contain:



LocID         LocName        JobID
------        -------        -----
44             Dallas         99
44             Dallas         100
55             Miami          99
55             Miami          100
55             Miami          101

I am creating pagation from the #tempa and need to also get the number of job openings for each LocID, so the example above would return



LocID         LocName        JobID      Openings
------        -------        -----      --------
44             Dallas         99           2
44             Dallas         100          2
55             Miami          99           3
55             Miami          100          3
55             Miami          101          3

Here is SELECT statement I would like to bring this into :


Select  #tempa.*,
		WordRadius = 
		 (
		   SELECT dbo.udf_Num_ToWords(MileRadius)
		  ),
		MoreRecords = 
		 (
		   SELECT COUNT(*)
		   FROM #tempa
		   WHERE RecCount >= @LastRec
		 ),
		TotalRecords=
		  (
		   SELECT COUNT(*)
		   FROM #tempa
		  )	
	FROM #tempa
	WHERE RecCount > @FirstRec AND RecCount < @LastRec

I already have two table scans on the #tempa and would like to get this and reduce the table scans.

Any steps in the right direction would be appreciated!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 18:16:53

So why not create an index on your table?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 18:22:39

Tara,

I am selecting into this #tempa, would it be useful to create an index after the selection? Or would it be better off to allow the #temp to be scanned?

I imagine there is a breaking point as to when and when not to create the index, but that is beyond my knowledge.

If I could guess the #tempa would have about 10,000 rows at the most, but the average would most likely be around 1,0000.

but I still need to get the additional count in there.

Thanks for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 18:32:34

You typically should create the temp table first with the indexes on it, then insert the rows into it.

If you are trying to avoid a table scan on a temp table, then you need to add an index. Without an index, SQL Server scans.

To see which way works best for you, you'll need to test it. We can't answer that for you.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 18:42:06

Tara,
Thanks for the advice.

I will try indexing the #tempa both ways and see where it gets me.

I am still stuck on getting the count for the number of jobs per location as I mentioned above. Still need help on that.

Thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 18:58:08

How is Openings calculated?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 19:13:58

Tara,
I guess I am not being clear. 'openings' is what I am trying to calculate.

If 'dallas' appears two times in the table then I need 'openings' to be 2,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 19:25:22

SELECT LocName, COUNT(*) AS Openings
FROM #tempa
GROUP BY LocName

If you could show us the query where you are inserting the data into the temp table, then we can add the above to it as well.

It doesn't look like there's a point in adding the Openings field to the select statement you posted as that doesn't show your expected result set anyway.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 19:34:06

Or perhaps this is what you want:


SELECT  t.*, 
	d.Openings,
	WordRadius = (SELECT dbo.udf_Num_ToWords(MileRadius)),
	MoreRecords = (SELECT COUNT(*) FROM #tempa WHERE RecCount >= @LastRec),
	TotalRecords = (SELECT COUNT(*) FROM #tempa)	
FROM #tempa t
INNER JOIN
(
	SELECT LocName, COUNT(*) AS Openings
	FROM #tempa
	GROUP BY LocName
) d
ON t.LocName = d.LocName
WHERE RecCount > @FirstRec AND RecCount < @LastRec

You should consider doing the MoreRecords and TotalRecords outside of this query though. Just stuff their values into a variable and output those in the stored procedure.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-09 : 20:01:06

Tara,

BINGO! The second one was what I was looking for. Thanks!

I knew how to get the count by the GROUP BY like you mentioned first, just could not work it like you had in the second code sample.

Thanks for the tip on MoreRecords and the TotalRecords. This sproc started out working fine, but I was dumping it into 4 #temps, just trying to refine and and thanks to the help here I am!!!!

Given what this sproc is doing I am very happy with it now. Although I think I need to test different ways on the #tempa index.

CREATE                  PROC [dbo].[SP_search_radi_Job]
		@CustID varchar (50),
		@Mode int = 0,
		@Zip char(8) ,
		@Miles int,
		@OrderBy int = 1,
		@SortAscDesc char (4) = 'asc',
		@RecsPerPage int = 250, 
		@Page int  =  1           
AS
SET NOCOUNT ON

DECLARE @HighLatitude float , @LowLatitude float, @HighLongitude float,  @LowLongitude float
DECLARE @StartLatitude float, @StartLongitude float, @LatitudeRange float, @LongitudeRange float
DECLARE @sql VARCHAR(64)
DECLARE @FirstRec int ,@LastRec int, @MoreRecords int, @TotalRecords int


SELECT @StartLatitude = a.lat, @StartLongitude = a.Lon
FROM places a
WHERE a.zip = @Zip


	Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
	Set @LatitudeRange = @Miles / 69.0454545454545454545454545454 --55  
	Set @LowLatitude = @StartLatitude - @LatitudeRange
	Set @HighLatitude = @StartLatitude + @LatitudeRange
	Set @LowLongitude = @StartLongitude - @LongitudeRange
	Set @HighLongitude = @StartLongitude + @LongitudeRange

SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)


SELECT  a.*, RecCount = identity(int,1,1)
INTO #tempa
FROM
	(
	  SELECT e.LocationName, e.LocationID, e.Address, e.City, e.State, b.Jobtitle, b.Summary, b.JobID,
						MileRadius = ROUND(dbo.F_GREAT_CIRCLE_DISTANCE(@StartLatitude,@StartLongitude,lat,lon),0)
	  FROM   dbo.JobDetail AS b INNER JOIN
					  dbo.JobsActive AS c INNER JOIN
					  dbo.Places AS d INNER JOIN
					  dbo.LocationDetail AS e ON d.ZIP = e.Zip ON c.LocationID = e.LocationID ON b.JobID = c.JobID
	  WHERE  (b.CustID = @CustID)  AND
					(b.ActiveFlag = 1) AND
					(Lat <= @HighLatitude AND Lat >= @LowLatitude)  AND 
					(Lon <= @HighLongitude AND Lon >= @LowLongitude)
					-- MileRadius < (@Miles * 1.05)   -- Drops bogus resul that are larger then 5% of the orginal radius
		) a 


CREATE UNIQUE CLUSTERED INDEX IX_1 ON #tempa (RecCount, LocationID, JobID)

SET @MoreRecords = (SELECT COUNT(*) FROM #tempa WHERE RecCount >= @LastRec)
SET @TotalRecords = (SELECT COUNT(*) FROM #tempa)

SELECT  t.*, 
		d.Openings,
		WordRadius = (SELECT dbo.udf_Num_ToWords(MileRadius)),
		MoreRecords = @MoreRecords,
		TotalRecords = @TotalRecords	
FROM #tempa t
	INNER JOIN
	(
		SELECT LocationID, COUNT(*) AS Openings
		FROM #tempa
		GROUP BY LocationID
	) d
	ON t.LocationID = d.LocationID
WHERE RecCount > @FirstRec AND RecCount < @LastRec

ORDER BY MileRadius , LocationName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources