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.

counting duplicate values

I know this question has been asked before but this is a little different and I can't seem to get my head around it right now.
What i have is a table like so:

ID1 ID2 DESC
100 24 something1
100 24 this is a test
100 24
100 25 somethingelse
101 36 something
101 37 something else altogether

What i need is to determine which ID1 value has the same ID2 value listed more than once WITH the description filled in. If there is no description filled in even though the ID2 may be listed twice on one ID1, then it's ok and don't want it displayed in the query.

So, in this case, ID1 of 100 has ID2 24 listed twice with a description on both ID2's (of 24). this is what I need to show up in the query like so:

ID1 ID2 DESC
100 24 something1
100 24 this is a test

Could someone give me a hand with this? I've found similiar problems...but haven't been able to apply those answers to this.

ThxUse something like r937's select statement in this post (http://www.dbforums.com/t1059709.html).

Have some fun.|||Try this:

select id1, id2 into #tmpGroup from table1
group by id1, id2
having count(1)>1

select id1, id2, desc from table1 t
inner join @.tmpGroup tmp on t.id1=tmp.id1 and t.id2=tmp.id2|||I usually do this with a subquery rather than a temp table:

select YourTable.*
from YourTable
inner join
(select ID1, ID2
from YourTable
where DESC is not null
group by ID1, ID2
having count(*) > 1) Dups
on YourTable.ID1 = Dups.ID1 and YourTable.ID2 = Dups.ID2

Same method, though...|||thx a ton yall! Once again, dBforums and Co has come to my rescue. I used blindman's example and tweaked it just a little for what I'm working on. I had to add on a "WHERE desc NOT LIKE '' " instead of the "desc is not null" in my situation. All i have to do now is only display exact duplicates in the instructions.

thx again.|||btw, are they ever gonna change this site so you can do more searches instead of having to wait 45 seconds all the time?|||You have zero length strings in DESC.

where len(DESC) > 0
...may be a little more efficient than NOT LIKE.|||thx, I'll give that a try. I got the query running exactly the way I want it to. Thx again yall.