Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    33

    Unanswered: Count number of matching entries in separate Table with one query?

    Hi, I have the following query.

    Code:
      $query = "SELECT #__jt_banners.id
                    , #__jt_banners.title
                    , #__jt_banners.img_location
                    , #__jt_banners.impressions
                    , #__jt_banners.start_date
                    , #__jt_banners.expiration_date
                    , #__jt_banners.published 
              FROM #__jt_banners";
    I need to count the number of times each (#__jt_banners.id = #__jt_banner_clicks.banner_id)

    Some banner entries will have clicks, some won't. I'd like to limit this to one query if possible, how should I go about doing this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT b.id
         , b.title
         , b.img_location
         , b.impressions
         , b.start_date
         , b.expiration_date
         , b.published 
         , COALESCE(bc.bclicks,0) AS bclicks
      FROM #__jt_banners AS b
    LEFT OUTER
      JOIN ( SELECT banner_id
                  , COUNT(*) AS bclicks
               FROM #__jt_banner_clicks
             GROUP
                 BY banner_id ) AS bc
        ON bc.banner_id = b.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    33
    didn't work

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Schweppesale View Post
    didn't work
    and i'm supposed to guess why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    33
    Code:
    SELECT #__jt_banners.id
                    , #__jt_banners.title
                    , #__jt_banners.img_location
                    , #__jt_banners.impressions
                    , #__jt_banners.start_date
                    , #__jt_banners.expiration_date
                    , #__jt_banners.published
                  
                    , #__jt_banner_clicks.banner_id
                    , COUNT(#__jt_banner_clicks.banner_id) as bann_cnt
              FROM #__jt_banners
                LEFT JOIN #__jt_banner_clicks ON
               (#__jt_banners.id = #__jt_banner_clicks.banner_id)
               GROUP BY #__jt_banners.id
    works now. don't worry about it

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please, do me the courtesy, since i took the time to help you for free, please at least explain why my query "didn't work"

    because i'm pretty sure it did
    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
  •