sql calculation query issue...

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

General SQL Server Forums

New to SQL Server Programming

sql calculation query issue...

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2011-05-27 : 08:42:29

Hi I have a table with some sample data as follows...

PAGETITLE HITCOUNT DATE
Homepage 3 2011-04-30
Homepage 35 2011-04-23
Homepage 10 2011-04-24
Homepage 5 2011-04-25
Homepage 36 2011-04-26
Homepage 100 2011-04-27
About Us 10 2011-04-30
About Us 12 2011-04-23
About Us 14 2011-04-18
About Us 10 2011-04-22
Services 6 2011-04-28
Services 32 2011-04-18
Services 17 2011-04-30
Services 33 2011-04-26

What I would like is a query that would return me the total hit count for each distint PAGETITLE over a delected date range and the percentage of it compared to the total. Bascially a list of the pages viewed on the site, in order of popularity.

So if i chose a date range between 2011-04-25 till 2011-04-30, I would get the follwing result

Pagetitle Views %of total pages
Homepage 44 40
About Us 10 9.1
Services 56 50.9

Is there a query that could do this in one to get the result above?

I'm quite struggling to do this in sql... any help would be much appreciated!

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 08:52:24

If you are on SQL 2005 or higher you can do the following. If not you will need to use a subquery to calculate the grand total and then use that to figure out the percentages.


CREATE TABLE #tmp (PAGETITLE VARCHAR(32), HITCOUNT INT, DATE DATETIME);
insert into #tmp values ('Homepage','3',' 2011-04-30');
insert into #tmp values ('Homepage','35',' 2011-04-23');
insert into #tmp values ('Homepage','10',' 2011-04-24');
insert into #tmp values ('Homepage','5',' 2011-04-25');
insert into #tmp values ('Homepage','36',' 2011-04-26');
insert into #tmp values ('Homepage','100',' 2011-04-27');
insert into #tmp values ('About Us','10',' 2011-04-30');
insert into #tmp values ('About Us','12',' 2011-04-23');
insert into #tmp values ('About Us','14',' 2011-04-18');
insert into #tmp values ('About Us','10',' 2011-04-22');
insert into #tmp values ('Services','6',' 2011-04-28');
insert into #tmp values ('Services','32',' 2011-04-18');
insert into #tmp values ('Services','17',' 2011-04-30');
insert into #tmp values ('Services','33',' 2011-04-26');

SELECT distinct
   PAGETITLE,
   SUM(Hitcount) OVER( PARTITION BY PAGETITLE) AS Hits,
   100.0*SUM (Hitcount) OVER( PARTITION BY PAGETITLE)/SUM (Hitcount) OVER() AS Percentage
FROM
   #tmp
WHERE
   date BETWEEN '2011-04-25' and '2011-04-30'
ORDER BY
   Hits desc;
drop table #tmp;

zubair
Yak Posting Veteran

67 Posts

Posted - 2011-05-27 : 08:56:09

Hi,

many thanks for your reply. I'm using sql server 2000 unfortunately.. Can you tell me how I would use the subquery?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 09:18:39

I don't have a SQL 2000 installation to test, but I think this would work.


SELECT
   PAGETITLE,
   SUM(HITCOUNT) Hits,
   100.0*SUM(HITCOUNT)/TotalCount Percentage
FROM
   #tmp
   CROSS JOIN
   ( SELECT SUM(HITCOUNT) TotalCount FROM #tmp 
     WHERE date BETWEEN '2011-04-25' AND '2011-04-30') B
WHERE 
   date BETWEEN '2011-04-25' and '2011-04-30'
GROUP BY
   PAGETITLE,
   TotalCount
ORDER BY
   Hits desc;

zubair
Yak Posting Veteran

67 Posts

Posted - 2011-05-27 : 09:30:51

Wow... thanks! It worked like a charm. You're sql skills ara amazing...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 09:48:54

Thanks for the kind words, and I am glad it worked out.

But, as to my skills at SQL, it is like the old saying, "the more you learn, the more you realize how much more there is to learn". There are people on this forum whose skill levels or depth of knowledge you or I may never achieve in a life time.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 18:48:11

>> Hi I have a table with some sample data as follows... <<
Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. And DATE is a reserved word. Is this what you meant, if you had followed Netiquette.

CREATE TABLE WebsiteHits
(page_title VARCHAR(20) NOT NULL,
polling_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (page_title, polling_date),
hit_cnt INTEGER NOT NULL CHECK (hit_cnt > 0));

>> What I would like is a query that would return me the total hit count for each distinct page_title over a selected date range and the percentage of it compared to the total. Basically a list of the pages viewed on the site, in order of popularity. <<

SELECT page_title,
SUM(hit_cnt) OVER(PARTITION BY page_title) AS page_hit_cnt,
SUM (hit_cnt) OVER() AS page_traffic_tot
FROM WebsiteHits
WHERE polling_date BETWEEN @in_start_date AND @in_end_date;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources