[SOLVED] Query HELP!!!

[flyerball10]

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

[akitchin]

how are you storing the click activity in the database?  hard to offer some query ideas when we don't know your database structure.

[flyerball10]

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

+-----------------+--------------+------+-----+---------------------+----------------+

[akitchin]

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.

[flyerball10]

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            |

+---------------------+--------+---------+------+------------+------------+

[akitchin]

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

[flyerball10]

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)