help modifiying query (PIVOT) - 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 modifiying query (PIVOT)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-27 : 16:11:17

Hi,

I have the following query below which is working great, but I wanted to add an extra 2 columns to it, but am having a little bit of difficulty.

The 2 columns I want to add are

TOTAL (is equal to to the total records for that day)
TOTAL_UNIQUE (is equal to the distinct IP total for that day )

I have included the table structure below. Any help on this is greatly appreciated !!

Thanks once again :)
mike123

CREATE PROCEDURE [dbo].[select_referrers_PIVOT_ByPage]
(
@numDays int
)
AS
SET NOCOUNT ON

DECLARE @horizontal TABLE
(
referDate datetime,
page int,
totalreferrers int
)

DECLARE @landingPageREGEX varchar(50)
SELECT @landingPageREGEX = ''

INSERT INTO @horizontal

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

SUBSTRING ( landingPage ,CHARINDEX('1_', landingPage) + 7 , CHARINDEX('.html', landingPage) - (CHARINDEX('2_', landingPage) + 7) ) as page,

COUNT(*) AS TotalReferrers

FROM tblreferrerDetails as land

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

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

GROUP BY CONVERT(varchar(10), land.queryDate, 112), SUBSTRING ( landingPage ,CHARINDEX('1_', landingPage) + 7 , CHARINDEX('.html', landingPage) - (CHARINDEX('2_', landingPage) + 7) )

ORDER BY referDate DESC

SELECT *
FROM @horizontal h
pivot
(
SUM(totalreferrers)
FOR page IN ([1], [2], [3], [4], [5], [6],

, [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30])
) p

ORDER BY referDate DESC
GO

-- table structure

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]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-28 : 01:37:21

COUNT(DISTINCT IP)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-28 : 02:57:01

You mean for each page value?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-28 : 16:01:21

Sorry I should have mentioned that I'm having problems integrating it into the query, I can get it in the select, but integrating with the PIVOT is causing me errors. I have very limited experience with PIVOTS and my few attempts dont seem to be working.

I don't need a value for each page, but if theres an easy way to do that, it would be helpful. That aside my main goal is to just get this working with a COUNT(*), and a COUNT(DISTINCT IP)

if anyone can help me adjust this query, its greatly appreciated..

thanks again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 01:55:33

As before, post same sample data and expected output based on the provided sample data.

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 04:48:24

Hey Guys,

Sorry should have included this, just rediscovered results to text in query analyzer, so its easier to post desired results :) As far as I know, I need a paid tool to create all the insert statements? I've always done them by hand =\, but for this query I dont think by hand is very manageable.

Basically the results right now look like this. I want to add 2 columns, named "TOTAL" and "TOTAL_UNIQUE"

TOTAL = count(*)
TOTAL_UNIQUE = COUNT(DISTINCT IP)

Hopefully this helps.

referDate               1           2           3           4           5           6           8           9           10          11          12          13          14          15          16          17          18          19          20          21          22          23          24          25          26          27          28          29          30
----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-09-29 00:00:00.000 133         NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        1
2008-09-28 00:00:00.000 2592        17          6           1           5           5           5           3           1           NULL        2           2           2           NULL        2           1           1           1           NULL        1           NULL        NULL        NULL        NULL        NULL        1           1           NULL        1
2008-09-27 00:00:00.000 2570        12          11          2           2           3           3           2           NULL        1           2           1           1           NULL        1           1           1           2           1           2           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        1
2008-09-26 00:00:00.000 2263        13          7           6           1           1           2           1           1           2           1           1           NULL        1           NULL        3           2           1           NULL        1           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
2008-09-25 00:00:00.000 2442        9           9           1           3           10          4           1           2           2           1           NULL        NULL        3           NULL        NULL        NULL        NULL        NULL        1           3           NULL        2           1           1           NULL        NULL        2           NULL
2008-09-24 00:00:00.000 2349        7           13          NULL        3           7           2           3           2           5           1           2           NULL        NULL        1           1           1           NULL        NULL        3           NULL        1           3           NULL        2           NULL        1           NULL        NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 05:22:33

quote:

Originally posted by mike123

Hey Guys,

Sorry should have included this, just rediscovered results to text in query analyzer, so its easier to post desired results :) As far as I know, I need a paid tool to create all the insert statements? I've always done them by hand =\, but for this query I dont think by hand is very manageable.

Basically the results right now look like this. I want to add 2 columns, named "TOTAL" and "TOTAL_UNIQUE"

TOTAL = count(*)
TOTAL_UNIQUE = COUNT(DISTINCT IP)

Hopefully this helps.

referDate               1           2           3           4           5           6           8           9           10          11          12          13          14          15          16          17          18          19          20          21          22          23          24          25          26          27          28          29          30
----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-09-29 00:00:00.000 133         NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        1
2008-09-28 00:00:00.000 2592        17          6           1           5           5           5           3           1           NULL        2           2           2           NULL        2           1           1           1           NULL        1           NULL        NULL        NULL        NULL        NULL        1           1           NULL        1
2008-09-27 00:00:00.000 2570        12          11          2           2           3           3           2           NULL        1           2           1           1           NULL        1           1           1           2           1           2           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        1
2008-09-26 00:00:00.000 2263        13          7           6           1           1           2           1           1           2           1           1           NULL        1           NULL        3           2           1           NULL        1           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
2008-09-25 00:00:00.000 2442        9           9           1           3           10          4           1           2           2           1           NULL        NULL        3           NULL        NULL        NULL        NULL        NULL        1           3           NULL        2           1           1           NULL        NULL        2           NULL
2008-09-24 00:00:00.000 2349        7           13          NULL        3           7           2           3           2           5           1           2           NULL        NULL        1           1           1           NULL        NULL        3           NULL        1           3           NULL        2           NULL        1           NULL        NULL

but this just shows a numeric value for each date for 1 to 30. what is this value?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 05:40:07

Hi visakh16,

This is the count of the total records for each date. The columns I want to add to this I also went them to be counts for the date. One a total count, and one a total unique(IP) count.

Please let me know if I can explain anything else

Thanks! :)
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 05:49:39

quote:
Originally posted by mike123

Hi visakh16,

This is the count of the total records for each date. The columns I want to add to this I also went them to be counts for the date. One a total count, and one a total unique(IP) count.

Please let me know if I can explain anything else

Thanks! :)
mike123

ok so you want for each date each time three values? 3 for 1,3 for2,...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 05:50:10

You can't use PIVOT for this. PIVOT has a huge drawback and that is it can only aggregate same kind of operation.
Try this

SELECT		referDate,
		SUM(CASE WHEN page = 1 THEN 1 ELSE 0 END) AS page1,
		SUM(CASE WHEN page = 2 THEN 1 ELSE 0 END) AS page2,
		SUM(CASE WHEN page = 3 THEN 1 ELSE 0 END) AS page3,
		SUM(CASE WHEN page = 4 THEN 1 ELSE 0 END) AS page4,
		SUM(CASE WHEN page = 5 THEN 1 ELSE 0 END) AS page5,
		SUM(CASE WHEN page = 6 THEN 1 ELSE 0 END) AS page6,
		SUM(CASE WHEN page = 7 THEN 1 ELSE 0 END) AS page7,
		SUM(CASE WHEN page = 8 THEN 1 ELSE 0 END) AS page8,
		SUM(CASE WHEN page = 9 THEN 1 ELSE 0 END) AS page9,
		SUM(CASE WHEN page = 10 THEN 1 ELSE 0 END) AS page10,
		SUM(CASE WHEN page = 11 THEN 1 ELSE 0 END) AS page11,
		SUM(CASE WHEN page = 12 THEN 1 ELSE 0 END) AS page12,
		SUM(CASE WHEN page = 13 THEN 1 ELSE 0 END) AS page13,
		SUM(CASE WHEN page = 14 THEN 1 ELSE 0 END) AS page14,
		SUM(CASE WHEN page = 15 THEN 1 ELSE 0 END) AS page15,
		SUM(CASE WHEN page = 16 THEN 1 ELSE 0 END) AS page16,
		SUM(CASE WHEN page = 17 THEN 1 ELSE 0 END) AS page17,
		SUM(CASE WHEN page = 18 THEN 1 ELSE 0 END) AS page18,
		SUM(CASE WHEN page = 19 THEN 1 ELSE 0 END) AS page19,
		SUM(CASE WHEN page = 20 THEN 1 ELSE 0 END) AS page20,
		SUM(CASE WHEN page = 21 THEN 1 ELSE 0 END) AS page21,
		SUM(CASE WHEN page = 22 THEN 1 ELSE 0 END) AS page22,
		SUM(CASE WHEN page = 23 THEN 1 ELSE 0 END) AS page23,
		SUM(CASE WHEN page = 24 THEN 1 ELSE 0 END) AS page24,
		SUM(CASE WHEN page = 25 THEN 1 ELSE 0 END) AS page25,
		SUM(CASE WHEN page = 26 THEN 1 ELSE 0 END) AS page26,
		SUM(CASE WHEN page = 27 THEN 1 ELSE 0 END) AS page27,
		SUM(CASE WHEN page = 28 THEN 1 ELSE 0 END) AS page28,
		SUM(CASE WHEN page = 29 THEN 1 ELSE 0 END) AS page29,
		SUM(CASE WHEN page = 30 THEN 1 ELSE 0 END) AS page30,
		COUNT(*) AS total,
		COUNT(DISTINCT IP) AS uniqueIP
FROM		(
			SELECT		CONVERT(CHAR(10), queryDate, 112) AS referDate,
					SUBSTRING(landingPage, CHARINDEX('1_', landingPage) + 7, CHARINDEX('.html', landingPage) - CHARINDEX('2_', landingPage) - 7) AS page,
					IP
			FROM		tblReferrerDetails
			WHERE		queryDate >= DATEADD(DAY, - @numDays, CONVERT(CHAR(10), GETDATE(), 112))
					AND (@landingPageREGEX IS NULL OR landingpage LIKE @landingPageREGEX)
		) AS d
GROUP BY	referDate
ORDER BY	referDate DESC

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-09-29 : 06:32:10

Hey Peso,

You nailed it yet again. Works perfectly. Much appreciated !!

thanks,
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources