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.