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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy