March 17, 2005 at 4:31 pm
Is there a method to cross join grouped queries and get the correct values?
Here is what I'm doing.
Query one:
SELECT BotName, COUNT(BotName) AS N
FROM dbo.SiteHits
GROUP BY BotName
Returns this:
BotName N
Yahoo! Slurp 221
MSNBOT 148
....
Query two:
SELECT COUNT(BotName) AS TTL
FROM dbo.MGD_SiteHits
WHERE (BotName IS NOT NULL)
Returns: TTL = 500
When I try to place both into the same query the results are all goofed up.
Query three:
SELECT dbo.MGD_SiteHits.BotName,
COUNT(dbo.MGD_SiteHits.BotName) AS N,
COUNT(MGD_SiteHits_1.BotName) AS TTL
FROM dbo.MGD_SiteHits CROSS JOIN
dbo.MGD_SiteHits MGD_SiteHits_1
GROUP BY dbo.MGD_SiteHits.BotName
Results are:
BotName N TTL
Yahoo Slurp! 220337 110721
MSNBOT 147556 74148
What's the fix for this?
March 17, 2005 at 9:08 pm
Hi,
Did you try using derived tables
select B.BotName
From
(SELECT BotName, COUNT(BotName) AS N
FROM dbo.SiteHits
GROUP BY BotName) as A Cross join
(SELECT BotName, COUNT(BotName) AS TTL
FROM dbo.MGD_SiteHits
WHERE (BotName IS NOT NULL)) as B
GROUP BY B.BotName
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
March 18, 2005 at 1:00 am
I think your query is wrong. I think what you want is this:
SELECT SiteHits.BotName,
COUNT(SiteHits.BotName) AS N,
COUNT(MGD_SiteHits.BotName) AS TTL
FROM SiteHits CROSS JOIN
MGD_SiteHits MGD_SiteHits
GROUP BY SiteHits.BotName
And I get the correct result which is: each botname in sitehits is combined with each botname from mgd_sitehits which are then grouped by botname in sitehits.
Cheers,
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply