Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Posts
    11

    Unanswered: complicated query

    Hi

    I dont know how to display advertisers ordered by their CTR. I've got 2 tables:

    advertisers
    ---------------------
    ID
    name


    banners
    ---------------------
    ID
    advertiserID
    banner_name
    clicks
    impressions

    CTR is calculated by summing all advertisers clicks and all advertiser impressions and divide clicks with impressions.

    Please, someone help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.name
         , SUM(b.clicks) AS sum_clicks
         , SUM(b.impressions) AS sum_impressions
         , SUM(b.clicks) 
           / SUM(b.impressions) AS CTR
      FROM advertisers AS a
    INNER
      JOIN banners AS b
        ON b.advertiserID = a.ID
    GROUP
        BY a.name
    ORDER
        BY CTR DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •