group by, count(*) on a modified column ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

group by, count(*) on a modified column ?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-31 : 04:47:30

Hi,

I have a table as seen at the bottom of this post.

The landingPage column has data in it in a specific format like '/new-products[_][0-9]%'

I want to query the data by date, and bring back data in such a format.

This is what I want me queried data to look like. These would obiously be the results for a much larger dataset than the sample data I typed below.

Any help on this is greatly appreciated... if I can provide any more details please let me know.

Thanks!
mike123

Date Page1 Page2 Page3 Page4 Page5 Page6 Page7 Page8 Page9 Page10
20080731 41 42 33 34 76 41 42 33 34 76
20080730 22 72 36 94 74 22 72 36 94 74
20080729 33 32 63 94 77 33 32 63 94 77
20080728 34 82 36 39 88 34 82 36 39 88
20080727 22 47 35 39 86 22 47 35 39 86
20080726 11 76 53 94 78 11 76 53 94 78
20080725 32 66 55 37 70 32 66 55 37 70

CREATE TABLE [dbo].[tblLandingPages](
[landID] [int] IDENTITY(1,1) NOT NULL,
[landingPage] [varchar](1000) NULL,
[landDate] [datetime] NULL

) ON [PRIMARY]

Sample Data, the table looks like this

landID,landingPage, landDate

1,/new-products_1.html,getDate()
2,/new-products_3.html,getDate()
3,/new-products_4.html,getDate()
4,/new-products_1.html,getDate()
5,/new-products_9.html,getDate()
6,/new-products_1.html,getDate()
7,/new-products_5.html,getDate()
8,/new-products_6.html,getDate()
9,/new-products_3.html,getDate()
10,/new-products_7.html,getDate()
11,/new-products_9.html,getDate()
12,/new-products_1.html,getDate()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:09:06

You have to explain what does this number means ?

quote:

Date Page1 Page2 Page3 Page4 Page5 Page6 Page7 Page8 Page9 Page10
20080731 41 42 33 34 76 41 42 33 34 76
20080730 22 72 36 94 74 22 72 36 94 74
20080729 33 32 63 94 77 33 32 63 94 77
20080728 34 82 36 39 88 34 82 36 39 88
20080727 22 47 35 39 86 22 47 35 39 86
20080726 11 76 53 94 78 11 76 53 94 78
20080725 32 66 55 37 70 32 66 55 37 70

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:11:48

Since you are using SQL Serer 2005, take a look at the PIVOT operator. use it

KH
[spoiler]Time is always against us[/spoiler]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-31 : 05:17:42

quote:

Originally posted by khtan

You have to explain what does this number means ?

quote:

Date Page1 Page2 Page3 Page4 Page5 Page6 Page7 Page8 Page9 Page10
20080731 41 42 33 34 76 41 42 33 34 76
20080730 22 72 36 94 74 22 72 36 94 74
20080729 33 32 63 94 77 33 32 63 94 77
20080728 34 82 36 39 88 34 82 36 39 88
20080727 22 47 35 39 86 22 47 35 39 86
20080726 11 76 53 94 78 11 76 53 94 78
20080725 32 66 55 37 70 32 66 55 37 70

KH
[spoiler]Time is always against us[/spoiler]

Sorry for not being more clear. The actual numbers in each row are just filler numbers. It would have taken 1000's of rows to make some filler data.

As far as the column headings..

Page1 is all "landingPage" columns that are like '/new-products_1%'
Page2 is all "landingPage" columns that are like '/new-products_2%'
Page3 is all "landingPage" columns that are like '/new-products_3%'

etc....... does this make sense? and yes I am using SQL2005 :) I will look into PIVOT operator now, but I don't think I am at the point to even use it yet on this query:) any further help is much appreciated.

Thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 05:30:08

And what's your rule for generating the filler numbers?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-31 : 05:37:46

quote:
Originally posted by visakh16

And what's your rule for generating the filler numbers?

Again sorry for not being more clear. The filler numbers are the counts of number of records that match the terms for each date.

Page1 is the Count "landingPage" columns that are like '/new-products_1%' for the specified date

Page2 is the Count "landingPage" columns that are like '/new-products_2%' for the specified date

Thanks!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 05:42:38

Ok. then i guess what you need is to use PIVOT and find Count(landID) for each of landing page values. And if number of landing pages are not certain you need to use PIVOT dynamically.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 05:51:43

as said, use PIVOT operator.
refer to BOL http://msdn.microsoft.com/en-us/library/ms177410.aspx?wt.slv=RightRail

KH
[spoiler]Time is always against us[/spoiler]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-31 : 05:59:14

quote:
Originally posted by visakh16

Ok. then i guess what you need is to use PIVOT and find Count(landID) for each of landing page values. And if number of landing pages are not certain you need to use PIVOT dynamically.

yes this seems to be exactly what I need to do.. my tsql skills aren't very good tho, having problem constructing this =[

any help greatly appreciated

thanks again,
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-31 : 11:12:42

Ok guys, been plugging away trial and error at this for awhile here.

I've got something to show.

This query below brings me back close to what I want, however the problem remains on the GROUP BY clause.

I need to TRIM or use REGEX on the GROUP BY for the landingPage column, so I can group by on just the integer in the string, so that I can finally apply the PIVOT operator. I am not sure the approach to take.

Any help is very much appreciated !!! :)
thx again
mike123

Current Results are like

20080731 /new-products_games_1.html 1
20080731 /new-products_books_1.html 3
20080731 /new-products_toys_1.html 1
20080731 /new-products_games_2.html 2
20080731 /new-products_books_2.html 4
20080731 /new-products_toys_2.html 2

All the 1's should be grouped together, and all the 2's should be grouped together. This goes for numbers all the way up to 50 or so.

DECLARE @landingPage varchar(50)
DECLARE @numDays int
SELECT @landingPage = '/new-products[_][0-9]%'
SELECT @numDays = 5

SELECT CONVERT(varchar(10), land.landDate, 112) as referDate,landingpage,

COUNT(*) AS TotalReferrers
FROM tblLandingPages as land

WHERE land.queryDate >= DateAdd(dd, - @numDays, CONVERT(varchar(10), getDate(), 112))

and (@landingpage is null or land.landingpage like @landingpage)

GROUP BY CONVERT(varchar(10), land.landDate, 112), landingPage

ORDER BY referDate DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 11:15:21

Something similar to this?

SELECT	p.landDate,
	p.Page1,
	p.Page2,
	p.Page3,
	p.Page4,
	p.Page5,
	p.Page6,
	p.Page7,
	p.Page8,
	p.Page9,
	p.Page10
FROM	(
		SELECT	landDate,
			REPLACE(REPLACE(landingPage, '/new-products_', 'Page'), '.html', '') AS thePage
		FROM	tblLandingPages
	) AS s
PIVOT	(
		COUNT(thePage)
		FOR thePage IN ([Page1], [Page2], [Page3], [Page4], [Page5], [Page6], [Page7], [Page8], [Page9], [Page10])
	) AS p

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-07-31 : 11:41:52

Hey Peso,

This is looking really good! It might exactly what I am looking for.

If I wanted to do the replace function for strings that are a little more dynamic, how could I do that ?

I'd like to group together some terms that I was previously using REGEX on before, but I'm not sure if you can incorporate REGEX into a replace statement?

Thanks very much!! huge help!!
mike123

For example:

20080731 /new-products_games_1.html 1
20080731 /new-products_books_1.html 3
20080731 /new-products_toys_1.html 1
20080731 /new-products_games_2.html 2
20080731 /new-products_books_2.html 4
20080731 /new-products_toys_2.html 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 16:35:05

[code]SELECT p.landDate,
p.Page1,
p.Page2,
p.Page3,
p.Page4,
p.Page5,
p.Page6,
p.Page7,
p.Page8,
p.Page9,
p.Page10
FROM (
SELECT landDate,
'Page' + LTRIM(STR(SUBSTRING(landingPage, PATINDEX('%_[0-9]%', landingPage) + 1, 2), 2, 0)) AS thePage
FROM tblLandingPages
) AS s
PIVOT (
COUNT(thePage)
FOR thePage IN ([Page1], [Page2], [Page3], [Page4], [Page5], [Page6], [Page7], [Page8], [Page9], [Page10])
) AS p[/code]

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-01 : 04:27:38

Hi Peso,

I was getting a "Error converting data type varchar to float." so I adjusted the query as shown below. (I have a wide range of data in this table, wider than I was able to post in this thread. I have limited the query with a WHERE clause so we can focus on just this type)

The line:

'Page' + LTRIM(STR(SUBSTRING(landingPage, PATINDEX('%_[0-9]', landingPage) + 1, 2), 2, 0)) AS thePage

I have changed it to

'Page' + LTRIM(STR(SUBSTRING(landingPage, PATINDEX('%_[0-9][.]html', landingPage) + 1, 2), 2, 0)) AS thePage

and the query is executing without errors. The problem is when we get past page 9 (two digit page #'s).

Page10 is being returned as page 0, as is page 29 getting returned as page 9.

Is this a simple fix ? Tried looking into PATINDEX etc, not exactly sure where to go from here.

Much appreciated as always!

Thanks,
Mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-01 : 10:07:46

Strange.... "2." with STR function should be converted to "2" only.

SELECT	p.landDate,
	p.Page1,
	p.Page2,
	p.Page3,
	p.Page4,
	p.Page5,
	p.Page6,
	p.Page7,
	p.Page8,
	p.Page9,
	p.Page10
FROM	(
		SELECT	landDate,
			'Page' + PARSENAME(REPLACE(landingPage, '_', '.'), 2) AS thePage
		FROM	tblLandingPages
	) AS s
PIVOT	(
		COUNT(thePage)
		FOR thePage IN ([Page1], [Page2], [Page3], [Page4], [Page5], [Page6], [Page7], [Page8], [Page9], [Page10])
	) AS p

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-01 : 11:08:55

quote:
Originally posted by Peso

Strange.... "2." with STR function should be converted to "2" only

Your correct. Im having problems with "15" being converted to "5"

Sorry for all the confusion. I should have layed it out more clearly like this. In Visual Basic I would Trim the String based on this:

The characters I am looking for occur between the last occurance of the "_" character, and the "." It will sometimes be 1 character in length, and sometimes 2.

Again, thanks very much for your patience!

Mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-01 : 13:35:19

got the substring function going like this :)

SUBSTRING ( landingPage ,CHARINDEX('keyword_', landingPage) + 7 , CHARINDEX('.html', landingPage) - (CHARINDEX('keyword_', landingPage) + 7) ) as thePage

now tuning the PIVOT operator :)

thanks again
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-01 : 16:43:33

What's wrong with the PARSENAME approach?

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-01 : 17:10:32

quote:
Originally posted by Peso

What's wrong with the PARSENAME approach?

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

I realized the string manipulation criteria was deeper than I initially posted.. when I ran this against it I was getting some NULL value returns .. i trust it works on what I posted.. sorry about that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources