Joining group queries

  • 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?

     

  • 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

  • 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