SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    976
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    displaying COUNT() as multiple column values for different types

    I've got a locator service that is tracking hit types when searches are performed. The table that does the recording is called locator_hits, with the actual dealer table called 'dealers'. I've been asked to create a monthly report with the following structure

    Code:
    --------------|  email  |  site   |  locate |  calc
    dealer 1      | COUNT() | COUNT() | COUNT() | COUNT()
    dealer 2      | COUNT() | COUNT() | COUNT() | COUNT()
    dealer 3      | COUNT() | COUNT() | COUNT() | COUNT()
    dealer 4      | COUNT() | COUNT() | COUNT() | COUNT()
    dealer 5      | COUNT() | COUNT() | COUNT() | COUNT()
    dealer etc.   | COUNT() | COUNT() | COUNT() | COUNT()
    Where the count is counting the hits for each dealer in the 'type' column. I'm using MySQL 5.0.77 and also have access to Coldfusion 9. Is this something that is easily accomplished within SQL directly or something that I need to involve the application server in? I can provide the table structures if that's any help but I guess my root question is can you use count to count specific groups with COUNT() and then output that as a column on a per row basis?

    Here's my first attempt, it's bad, but I thought something like a 'nested select' statement would be the answer (it's not).
    Code SQL:
    SELECT DISTINCT(t1.dealer_id), 
    	(SELECT COUNT(*)
    	FROM locator_hits 
    	WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
    	AND TYPE='email_contact')
    	AS `email`
    FROM locator_hits t1
    LEFT JOIN dealers t2 ON t1.dealer_id = t2.id
    WHERE t1.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
    ORDER BY dealer_id

    Thanks for any help or pointers.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  2. #2
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    976
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay found a hint from stackOverflow, but i'm getting random duplicate rows… and the numbers are WAY off. Here's the query I currently have.
    Code SQL:
    SELECT     
    	dealer_id, t2.name,
         SUM(CASE t1.TYPE WHEN 'locator_search' THEN 1 ELSE 0 END) AS 'locator hits',
         SUM(CASE t1.TYPE WHEN 'email_contact' THEN 1 ELSE 0 END) AS 'email contacts', 
         SUM(CASE t1.TYPE WHEN 'site_forward' THEN 1 ELSE 0 END) AS 'site forwards',
         SUM(CASE t1.TYPE WHEN 'calculator_search' THEN 1 ELSE 0 END) AS 'calculator hits'
    FROM  locator_hits t1
    LEFT JOIN dealers t2 ON t1.dealer_id = t2.id
    WHERE t1.created_at BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
    GROUP BY DATE(t1.created_at)
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    I... THINK i understand what you're trying to do here, but....

    Your group by is by a date, should be by dealer id... your tables are joined backwards (You want every dealer, whether or not they have any hits.. which would be dealer LEFT JOIN hits)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT d.id AS dealer
         , SUM(CASE WHEN lh.type = 'locator_search'    THEN 1 ELSE 0 END) AS 'locator hits'
         , SUM(CASE WHEN lh.type = 'email_contact'     THEN 1 ELSE 0 END) AS 'email contacts' 
         , SUM(CASE WHEN lh.type = 'site_forward'      THEN 1 ELSE 0 END) AS 'site forwards'
         , SUM(CASE WHEN lh.type = 'calculator_search' THEN 1 ELSE 0 END) AS 'calculator hits'
      FROM dealers AS d
    LEFT OUTER
      JOIN locator_hits AS lh
        ON lh.dealer_id = d.id
       AND lh.created_at BETWEEN CURRENT_DATE - INTERVAL 30 DAY
                             AND NOW()
    GROUP 
        BY d.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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