help with group by - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help with group by

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-30 : 08:57:06

Hi,

I have the table below on which I am running queries against. What I am looking to do is some string manipulation against the "landingPage" column, and group the landingPage GROUP BY by directory. For example, the following data inserted below

INSERT INTO tbl_ReferrerDetails (landingPage,queryDate) VALUES ('/1.aspx', getDate())

INSERT INTO tbl_ReferrerDetails (landingPage,queryDate) VALUES ('/a/1.aspx', getDate())

INSERT INTO tbl_ReferrerDetails (landingPage,queryDate) VALUES ('/a/2.aspx', getDate())

INSERT INTO tbl_ReferrerDetails (landingPage,queryDate) VALUES ('/a/3.aspx', getDate())

INSERT INTO tbl_ReferrerDetails (landingPage,queryDate) VALUES ('/b/1.aspx', getDate())

INSERT INTO tbl_ReferrerDetails (landingPage,queryDate) VALUES ('/b/6.aspx', getDate())

would return

landingPage / referCount

"/" / "1"
"/a/" / "3"
"/b/" / "2"

when the following is run

SELECT landingPage,count(*) AS referCount FROM tbl_referrerDetails

GROUP BY landingPage ORDER BY queryCount DESC

against this table below

CREATE TABLE [dbo].[tbl_ReferrerDetails](
[queryID] [int] IDENTITY(1,1) NOT NULL,
[referrerURL] [varchar](1000) NULL,
[query] [varchar](1000) NULL,
[landingPage] [varchar](1000) NULL,
[queryDate] [datetime] NULL,
[IP] [varchar](15) NULL
) ON [PRIMARY]

thanks very much once again for any assitance available!! :)

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 09:23:48

[code]-- Prepare sample data
DECLARE @Sample TABLE (Path VARCHAR(100))

INSERT @Sample
SELECT '/1.aspx' UNION ALL
SELECT '/a/1.aspx' UNION ALL
SELECT '/a/2.aspx' UNION ALL
SELECT '/a/3.aspx' UNION ALL
SELECT '/b/1.aspx' UNION ALL
SELECT '/b/6.aspx'

-- Show the expected output
SELECT d.Path AS LandingPage,
COUNT(*) AS ReferCount
FROM (
SELECT SUBSTRING(Path, 1, LEN(Path) - CHARINDEX('/', REVERSE(Path)) + 1) AS Path
FROM @Sample
) AS d
GROUP BY d.Path
ORDER BY d.Path[/code]

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-30 : 09:52:12

Hi Peso,

thanks so much!, worked perfectly!! :)

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources