2 query, 2 different resulsets... why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

2 query, 2 different resulsets... why?

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-09 : 17:46:40

On my web site, a friend can send an email to his friend to tell him to visit my web site. When he does that, I insert a row in my Table LSentT with his customer_key, sent_key, visit_key, email...

Uppon reception of this email, I insert a row into my Table SAVisitT whit the visit_key of the friend, sent_key, session_key. The session_key identify the cookie. So, one could make two visits with the same sent_key, he we would get two different visit_key, but the session_key and the sent_key would be the same.

I want to make a query that returns the total of the visit generated from the sent_key, and also the unique visit generated.

Here is my first query:
select
count(distinct visit_key),
count(distinct session_key)
from savisitT
where sent_key is not null

So far this query returns 93 unique and 166 totals(one could click more than once on the invitation)

Now I want to know how many visit a customer, based on his visit, generates trafic on my web site.
Like visit_key, sent_key, visitUnique,VisitTotal. If I sum up all the rows I expect to have the results as the one from the first query
Here comes the second query:
select
sum(visitUnique) as VisitUnique,
sum(visitTotal) as VisitTotal
from (
SELECT
LSentT.Visit_key,
LSentT.Sent_key,
COUNT(SAVisitT.Sent_key) AS VisitTotal,
COUNT(DISTINCT SAVisitT.Session_key) AS VisitUnique
FROM
LSentT INNER JOIN
SAVisitT ON LSentT.Sent_key = SAVisitT.Sent_key
GROUP BY LSentT.Visit_key, LSentT.Sent_key
) a
But when I sum up all the rows I get these results: VisitUnique 114 and visitTotal 166. I should get 93 visitUnique. Can't find why! Here are the table definition.

CREATE TABLE [SAVisitT] (
[Visit_key] [char] (14) COLLATE Compatibility_52_409_20003 NOT NULL ,
[Version_key] [char] (12) COLLATE Compatibility_52_409_20003 NULL ,
[Sent_key] [int] NULL ,
[Session_key] [char] (33) COLLATE Compatibility_52_409_20003 NOT NULL ,
[VisitType_key] [smallint] NOT NULL ,
[DateStamp] [datetime] NOT NULL ,
CONSTRAINT [PK_SAVisitT] PRIMARY KEY CLUSTERED
(
[Visit_key]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [LSentT] (
[Sent_key] [int] NOT NULL ,
[SentType_key] [char] (1) COLLATE Compatibility_52_409_20003 NOT NULL ,
[Visit_key] [char] (14) COLLATE Compatibility_52_409_20003 NULL ,
[Card_key] [int] NULL ,
[Customer_key] [int] NULL ,
[Language_key] [tinyint] NOT NULL CONSTRAINT [DF_LSentT_Language_key] DEFAULT (2),
[Product_key] [smallint] NOT NULL ,
[Country_key] [smallint] NULL ,
[ProspectSolicited_key] [int] NULL ,
[Email] [char] (100) COLLATE Compatibility_52_409_20003 NOT NULL ,
[Message] [nvarchar] (3000) COLLATE Compatibility_52_409_20003 NULL ,
[random] [numeric](18, 0) NULL ,
[DateStamp] [datetime] NULL CONSTRAINT [DF_LSentT_DateStamp] DEFAULT (getdate()),
CONSTRAINT [PK_OSentT] PRIMARY KEY CLUSTERED ) on primary

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-09 : 18:37:47

A count does not equal the sum unless you have 1 or 0 for the the values you are counting.

Also what do you get when you run this.
SELECT
LSentT.Visit_key,
LSentT.Sent_key,
COUNT(SAVisitT.Sent_key) AS VisitTotal,
COUNT(DISTINCT SAVisitT.Session_key) AS VisitUnique
FROM
LSentT INNER JOIN
SAVisitT ON LSentT.Sent_key = SAVisitT.Sent_key
GROUP BY LSentT.Visit_key, LSentT.Sent_key

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-09 : 18:51:40

same 114, 166

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-10 : 15:26:59

OK I finally found my error. Because I was grouping on visit_key and sent_key, some session_key made visits from 2 sent_key. This is the reason why when I was counting only on SAVisitT I was getting 93 unique person, but because of the group by, on the join, some session_key are count twice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources