ASP SQL Statement Count Help - SQL Server Forums

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

Development Tools

ASP.NET

ASP SQL Statement Count Help

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2005-02-23 : 13:31:18

I currently have this sql statement that counts the total records.

SELECT SUM(ADCount) AS [ADCount] from tbl_AD_Stats WHERE ID = " & request("ID")

I want to do the same thing but by another field called pageid. Basically on the website I have a pageid for certian pages I collect these stats.

I could do this by adding another where statement but the pageid can be variable based on the id. Basically some ID's will have three page IDs and other might just have 1.

I want a report that counts the number of records and then splits it up by the various pageid for that particular ID.

Hope that makes sense. Any help would be greatly appreciated!

Jim

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-02-24 : 00:12:43

I would recomend doing this with a stored proc and passing the parameters ID and pageid to the stored proc.

With PageID having possibly multiple id's - you could pass them as a Comma seperated string and then have a UDF that returns this comma seperated string as a table variable - I use this often - here is an example of how the function code could look:

--************************************
CREATE FUNCTION dbo.ConvertDelimmitedStringToIntTable(@String VARCHAR(4000), @FieldTerminator CHAR(1))
RETURNS @RetTable TABLE(TempID INT)
AS
BEGIN
DECLARE @CharInx INT,
@ComPos INT,
@TempInt INT

SET @CharInx = 1

WHILE @CharInx <= LEN(@String)
BEGIN
SET @ComPos = (CHARINDEX(@FieldTerminator, @String, @CharInx))
IF @ComPos = 0
BEGIN
SET @ComPos = LEN(@String) + 1
END
INSERT INTO @RetTable
SELECT LTRIM(RTRIM(STR(LTRIM(RTRIM(SUBSTRING(@String, @CharInx, @ComPos - @CharInx))), 8)))
SET @CharInx = @ComPos + 1
END

RETURN
END
--************************************

Right - so your sp code would then be something like this.

CREATE Procedure Proc1
@ID INT,
@PageID VARCHAR(4000)
AS
SELECT
a.PageID,
SUM(a.ADCount) AS [ADCount]
from tbl_AD_Stats AS a
JOIN dbo.ConvertDelimmitedStringToIntTable(@PageID, ',') as b
On a.PageID = b.TempID
WHERE ID = @ID
GROUP BY
a.PageID

--******************************************************************

Stored Procs are just so much neater than Freehand SQL

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources