flyerball10 Posted July 19, 2007 Share Posted July 19, 2007 I need to generate a query that shows the total of 'click' activity for the previous year up-to date group by the 'desination_url'. Here is an example. +---------------------+--------+---------+------+------------+------------+ | destination_url | July | June | May | April | March | +---------------------+--------+---------+------+------------+------------+ | www.yahoo.com | 15 | 15 | 12 | 9 | 12 | | www.phpfreaks.com | 23 | 33 | 10 | 7 | 10 | +---------------------+--------+---------+------+------------+------------+ Kind of like a cross tab query of some sort. Please help. I'm stuck Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 19, 2007 Share Posted July 19, 2007 how are you storing the click activity in the database? hard to offer some query ideas when we don't know your database structure. Quote Link to comment Share on other sites More sharing options...
flyerball10 Posted July 19, 2007 Author Share Posted July 19, 2007 you are right, sorry. here is the structure for the 'click' table +-----------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------------------+----------------+ | click_id | int(11) | | PRI | NULL | auto_increment| | datetime | datetime | | MUL | 0000-00-00 00:00:00 | | | destination_url | varchar(255) | | MUL | | | +-----------------+--------------+------+-----+---------------------+----------------+ Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 19, 2007 Share Posted July 19, 2007 you'll probably want to COUNT() the rows with the destination URL of interest, grouping by the month of the datetime. give this a shot: SELECT COUNT(click_id) AS total_clicks, DATE_FORMAT(datetime, '%M') AS month_name, DATE_FORMAT(datetime, '%m') AS month_num FROM table WHERE destination_url = 'something.com' GROUP BY month_name ORDER BY month_num i'll be honest, i usually have to fudge with my queries to get them to work properly. hopefully someone with better knowledge comes around, but i think this should work. Quote Link to comment Share on other sites More sharing options...
flyerball10 Posted July 19, 2007 Author Share Posted July 19, 2007 that produces the following results: +--------------+------------+-----------+ | total_clicks | month_name | month_num | +--------------+------------+-----------+ | 865 | January | 01 | | 886 | February | 02 | | 1559 | March | 03 | | 1783 | April | 04 | | 2228 | May | 05 | | 2519 | June | 06 | | 1757 | July | 07 | | 1329 | August | 08 | | 950 | September | 09 | | 837 | October | 10 | | 1309 | November | 11 | | 1283 | December | 12 | +--------------+------------+-----------+ 12 rows in set (14.66 sec) I'm looking to have the results grouped by the 'destination_url' and then show each month as a column with the total. Here is an example below: +---------------------+--------+---------+------+------------+------------+ | destination_url | July | June | May | April | March | +---------------------+--------+---------+------+------------+------------+ | www.yahoo.com | 15 | 15 | 12 | 9 | 12 | | www.phpfreaks.com | 23 | 33 | 10 | 7 | 10 | +---------------------+--------+---------+------+------------+------------+ Quote Link to comment Share on other sites More sharing options...
akitchin Posted July 19, 2007 Share Posted July 19, 2007 perhaps we'll need to add grouping by destination_url. keep in mind i'm hoping some SQL guru (read: fenway or barand) swoops in at any time and corrects my retardation. SELECT COUNT(click_id) AS total_clicks, DATE_FORMAT(datetime, '%M') AS month_name, DATE_FORMAT(datetime, '%m') AS month_num, destination_url FROM table GROUP BY destination_url, month_name ORDER BY destination_url ASC, month_num ASC Quote Link to comment Share on other sites More sharing options...
flyerball10 Posted July 20, 2007 Author Share Posted July 20, 2007 I figured it out....in case anyone is interested.... mysql> SELECT destination_url, -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '07-2007', 1, 0)) AS "JUL 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '06-2007', 1, 0)) AS "JUN 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '05-2007', 1, 0)) AS "MAY 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '04-2007', 1, 0)) AS "APR 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '03-2007', 1, 0)) AS "MAR 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '02-2007', 1, 0)) AS "FEB 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '01-2007', 1, 0)) AS "JAN 2007", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '12-2006', 1, 0)) AS "DEC 2006", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '11-2006', 1, 0)) AS "NOV 2006", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '10-2006', 1, 0)) AS "OCT 2006", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '09-2006', 1, 0)) AS "SEP 2006", -> SUM(IF(DATE_FORMAT(datetime, '%m-%Y') = '08-2006', 1, 0)) AS "AUG 2006" -> FROM click -> WHERE site_id = 8 AND datetime >= '2006-08-01' -> GROUP BY destination_url -> ORDER BY 'JUN 2007' DESC -> LIMIT 15; +-------------------------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+ | destination_url | JUL 2007 | JUN 2007 | MAY 2007 | APR 2007 | MAR 2007 | FEB 2007 | JAN 2007 | DEC 2006 | NOV 2006 | OCT 2006 | SEP 2006 | AUG 2006 | +-------------------------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+ | http://www.alconox.com | 46 | 63 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 53 | 70 | | http://www.atperpg.com | 34 | 53 | 82 | 85 | 22 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.ManufacturingWeek.com/medical | 30 | 44 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.BIOMEDeviceShow.com | 25 | 44 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.minco.com/DesignGuide | 31 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.idex-hs.com/catalog | 20 | 34 | 35 | 36 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.newageindustries.com/canon | 30 | 33 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.caplugs.com | 15 | 30 | 56 | 43 | 25 | 15 | 7 | 21 | 8 | 5 | 10 | 9 | | http://www.southco.com/EASoffer2 | 36 | 29 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.tempco.com | 16 | 27 | 29 | 28 | 21 | 10 | 6 | 10 | 8 | 4 | 6 | 6 | | http://www.exaltcustomtubing.com | 13 | 24 | 0 | 0 | 21 | 20 | 16 | 12 | 7 | 8 | 20 | 22 | | http://www.seamcraft.com | 14 | 24 | 28 | 27 | 20 | 8 | 8 | 10 | 9 | 5 | 8 | 6 | | http://www.plasmaetch.com | 17 | 23 | 29 | 26 | 20 | 10 | 7 | 18 | 11 | 5 | 6 | 6 | | http://www.ironwoodelectronics.com/mdm_card.htm | 15 | 23 | 30 | 27 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | http://www.gmplabeling.com | 12 | 22 | 28 | 32 | 22 | 10 | 4 | 15 | 9 | 5 | 16 | 21 | +-------------------------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+ 15 rows in set (2.91 sec) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.