Counting Occurences

I'm having trouble getting my head around this, and no one in the groups
has posted exactly the problem.

The table below tracks site traffic across a network. There is 1 row
per pageview and UUID is that user's unique cookie.

CREATE TABLE [dbo].[Stats_Working] (
[inac_stats_id] [int] NOT NULL ,
[hit_time] [datetime] NULL ,
[site_id] [int] NULL ,
[site_cat_id] [int] NULL ,
[item_id] [int] NULL ,
[local_content_cat_id] [int] NULL ,
[UUID] [float] NULL ,
[USER_AGENT] [char] (50) NULL
) ON [PRIMARY]
GO

A client asked: of these pageviews and within each category, how many
are accounted for by users that generated 2 or fewer pageviews, 3+
pageviews, and 4+ pageviews?

I said, "yes, we have that information". I know it's here, but I'm
braindead from looking at what should be a simple solution.

Thanks for any help
Cam Bevis

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Cam Bevis (anonymous@.devdex.com) writes:
> I'm having trouble getting my head around this, and no one in the groups
> has posted exactly the problem.
> The table below tracks site traffic across a network. There is 1 row
> per pageview and UUID is that user's unique cookie.
> CREATE TABLE [dbo].[Stats_Working] (
> [inac_stats_id] [int] NOT NULL ,
> [hit_time] [datetime] NULL ,
> [site_id] [int] NULL ,
> [site_cat_id] [int] NULL ,
> [item_id] [int] NULL ,
> [local_content_cat_id] [int] NULL ,
> [UUID] [float] NULL ,
> [USER_AGENT] [char] (50) NULL
> ) ON [PRIMARY]
> GO
> A client asked: of these pageviews and within each category, how many
> are accounted for by users that generated 2 or fewer pageviews, 3+
> pageviews, and 4+ pageviews?
> I said, "yes, we have that information". I know it's here, but I'm
> braindead from looking at what should be a simple solution.

If I understand this right, and am not too confused at this late hour:

SELECT COUNT(*), no_of_views
FROM (SELECT no_of_views = CASE WHEN cnt <= 2 THEN '<= 2 pagewiews'
WHEN cnt = 3 THEN '= 3 pageviews'
ELSE '>= 4 pageviews'
END
FROM (SELECT cnt, COUNT(*)
FROM Stats_Working
GROUP BY UUID) AS a) AS b
GROUP BY no_of_views

So I am cheating a bit. You don't get 3+ pageviews, but 3 exactly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, but not exactly.

What we're trying to return something more like

(pivot this into columns, no room on this editor)

Total Pageviews: 100,000
Pageviews by users that generated 2 or less pageviews:60,000
Pageviews by users that generated 3 or more pageviews:40,000
Pageviews by users that generated 3 or more pageviews:20,000

The trick is counting the occurences of the UUID (unique cookie) in the
table and... select count [distinct?[(uuid)...where count
[distinct?](uuid)<=2,...

something like. I know it's there somewhere.

I guess the goal is best stated as seeing how many pageviews are
generated by people that poke around for a while(or not), and putting
them into groups. It's some kind of metric.

Thanks for your response!

Cam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Cam Bevis (anonymous@.devdex.com) writes:
> Thanks, but not exactly.
> What we're trying to return something more like
> (pivot this into columns, no room on this editor)
> Total Pageviews: 100,000
> Pageviews by users that generated 2 or less pageviews:60,000
> Pageviews by users that generated 3 or more pageviews:40,000
> Pageviews by users that generated 3 or more pageviews:20,000
>
> The trick is counting the occurences of the UUID (unique cookie) in the
> table and... select count [distinct?[(uuid)...where count
> [distinct?](uuid)<=2,...
> something like. I know it's there somewhere.

It would help if you could provide sample data in form of INSERT statements
and the then desired output from that sample output. That would make it
easier for anyone who tries it to test a solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Cam Bevis" wrote:
> I'm having trouble getting my head around this, and no one in the groups
> has posted exactly the problem.
> The table below tracks site traffic across a network. There is 1 row
> per pageview and UUID is that user's unique cookie.
> CREATE TABLE [dbo].[Stats_Working] (
> [inac_stats_id] [int] NOT NULL ,
> [hit_time] [datetime] NULL ,
> [site_id] [int] NULL ,
> [site_cat_id] [int] NULL ,
> [item_id] [int] NULL ,
> [local_content_cat_id] [int] NULL ,
> [UUID] [float] NULL ,
> [USER_AGENT] [char] (50) NULL
> ) ON [PRIMARY]
> GO
> A client asked: of these pageviews and within each category, how many
> are accounted for by users that generated 2 or fewer pageviews, 3+
> pageviews, and 4+ pageviews?
> I said, "yes, we have that information". I know it's here, but I'm
> braindead from looking at what should be a simple solution.

I ignored the "within each category" requirement above, but this should get
you going:

CREATE TABLE [dbo].[Stats_Working] (
[inac_stats_id] [int] NOT NULL ,
[hit_time] [datetime] NULL ,
[site_id] [int] NULL ,
[site_cat_id] [int] NULL ,
[item_id] [int] NULL ,
[local_content_cat_id] [int] NULL ,
[UUID] [float] NULL ,
[USER_AGENT] [char] (50) NULL
) ON [PRIMARY]
GO

insert [Stats_Working] values (0, null, 0, 0, 0, 0, 1.0, '')

insert [Stats_Working] values (0, null, 0, 0, 0, 0, 2.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 2.0, '')

insert [Stats_Working] values (0, null, 0, 0, 0, 0, 3.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 3.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 3.0, '')

insert [Stats_Working] values (0, null, 0, 0, 0, 0, 4.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 4.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 4.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 4.0, '')

insert [Stats_Working] values (0, null, 0, 0, 0, 0, 5.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 5.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 5.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 5.0, '')
insert [Stats_Working] values (0, null, 0, 0, 0, 0, 5.0, '')

select UUID, count(*) as 'hits'
from Stats_working
group by UUID

select sum(case when sub.hits <= 2 then sub.hits else 0 end) '2 or fewer',
sum(case when sub.hits >= 3 then sub.hits else 0 end) '3 or more',
sum(case when sub.hits >= 4 then sub.hits else 0 end) '4 or more'
from (
select UUID, count(*) as 'hits'
from Stats_working
group by UUID
)
AS sub
go

--RESULTS--

UUID hits
-- ---
1.0 1
2.0 2
3.0 3
4.0 4
5.0 5

2 or fewer 3 or more 4 or more
---- ---- ----
3 12 9|||I think a tiny change to Erland's solution might do the trick...

SELECT TotalViews = SUM(cnt), no_of_views
FROM (SELECT cnt, no_of_views = CASE WHEN cnt <= 2 THEN '<= 2 pagewiews'
WHEN cnt = 3 THEN '= 3 pageviews'
ELSE '>= 4 pageviews'
END
FROM (SELECT cnt = COUNT(*)
FROM Stats_Working
GROUP BY UUID) AS a) AS b
GROUP BY no_of_views

So I am cheating a bit. You don't get 3+ pageviews, but 3 exactly.