displaying COUNT() as multiple column values for different types

[downtroden]

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

--------------|  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).

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.

[downtroden]

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.

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)

[StarLion]

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)

[r937]

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