help with query (add % value for count /each row)

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 query (add % value for count /each row)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-02-21 : 08:10:08

Hi,

I have the following query, which brings back data in two columns. "landingPage" & "referCount"

I want to add a 3rd column, which would be named "percentOfTotal"

I want this value to be as described, but I'm not sure how to integrate it into this query. Could anyone give me some assistance here?

Much appreciated!!

Thanks again,
mike123




CREATE PROCEDURE [dbo].[select_google_referrers_topDirectories] 3
	(
	 @daysAgo int
	 )
AS SET NOCOUNT ON 


SELECT		d.landingPage AS LandingPage,
		COUNT(*) AS ReferCount
FROM		(
			SELECT	SUBSTRING(landingPage, 1, LEN(landingPage) - CHARINDEX('/', REVERSE(landingPage)) + 1) AS landingPage
			FROM	tblgoogle_referrerDetails

		
WHERE DateDiff(dd, queryDate, GetDate()) < @daysAgo

		) AS d
GROUP BY	d.landingPage
ORDER BY	referCount DESC

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 08:29:23

[code]Select
t1.LandingPage,
t1.ReferCount,
(t1.ReferCount*1.0/t2.[Total])*100.0 as percentOfTotal
From
(
SELECT d.landingPage AS LandingPage,
COUNT(*) AS ReferCount
FROM (
SELECT SUBSTRING(landingPage, 1, LEN(landingPage) - CHARINDEX('/', REVERSE(landingPage)) + 1) AS landingPage
FROM tblgoogle_referrerDetails

WHERE DateDiff(dd, queryDate, GetDate()) < @daysAgo

) AS d
GROUP BY d.landingPage
) t1
cross join (Select count(*) as [Total] from tblgoogle_referrerDetails WHERE DateDiff(dd, queryDate, GetDate()) < @daysAgo) t2
ORDER BY t1.referCount DESC[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-02-21 : 08:56:49

harsh_athalye,

works perfectly!

thanks once again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-21 : 09:06:22

And without CROSS JOIN nor subqueries

DECLARE @Sample TABLE (LandingPage VARCHAR(100))

INSERT	@Sample
SELECT	'http://www.developerworkshop.net' UNION ALL
SELECT	'http://www.sqlteam.com' UNION ALL
SELECT	'http://www.sqlteam.com'

SELECT		LandingPage,
		Local,
		1.0 * Local / Total AS Perc
FROM		(
			SELECT DISTINCT	LandingPage,
					COUNT(*) OVER (PARTITION BY LandingPage) AS Local,
					COUNT(*) OVER () AS Total
			FROM		@Sample
		) AS d

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-21 : 09:13:30

And to make use of present index over QueryDate

CREATE PROCEDURE dbo.select_google_referrers_topDirectories
(
	@DaysAgo int
)
AS

SET NOCOUNT ON 

SELECT		e.LandingPage,
		e.ReferCount,
		1.0 * e.ReferCount / e.Total AS percentOfTotal
FROM		(
			SELECT DISTINCT	d.landingPage AS LandingPage,
					COUNT(*) OVER (PARTITION BY d.landingPage) AS ReferCount,
					COUNT(*) OVER () AS Total
			FROM		(
						SELECT	SUBSTRING(landingPage, 1, LEN(landingPage) - CHARINDEX('/', REVERSE(landingPage)) + 1) AS landingPage
						FROM	tblgoogle_referrerDetails
						WHERE	queryDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GetDate()) - @daysAgo, '19000101') 
					) AS d
		) AS e
ORDER BY	e.ReferCount DESC

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 09:08:30

Did you notice any speed difference?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources