% of duplicate rows... 2

[SidTheSquid]

Hi, I have a table with a list of pages (from a web traffic log) and I wish to create an sql statement that list the percentage that a certain page was hit.

I wish to display something like this

Page Hits Percentage
------------------------------------
default.aspx 100 66
frmtest1.aspx 25 16
List.aspx 25 16


I know the first part which is:
Select Page, count(page_name) as Hits from myTable group by Page

But I'm having trouble getting the percentage.
Anybody out there that can help me, that would be great.

Thanks
Sid

 

[JamesLean]

Try this:

SELECT page,
  COUNT(*) AS hits,
  100.0 * COUNT(*) / (SELECT COUNT(*) FROM tbl) AS percentage
FROM tbl
GROUP BY page
ORDER BY percentage DESC

--James

 

[SidTheSquid]

Thanks man. You're the best !!

 

[clarkin]

.. and if you have a lot of pages and/or want to make this more efficient you could try and avoid the sub-SELECT COUNT(*) being run for every row like so:

DECLARE @totalNum int

SELECT @totalNum = COUNT(*) FROM tbl

SELECT page,
  COUNT(*) AS hits,
  100.0 * COUNT(*) / @totalNum AS percentage
FROM tbl
GROUP BY page
ORDER BY percentage DESC

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[SidTheSquid]

hmm... thanks for the tip. I never knew you could declare variables like that.

Thanks again.

Sid