mySQL Select visits and display all by month?

Question

Hi guys,

I'm punching way above my weight for mySQL Queries and syntax right now. I could do with some help achieving the following:-

I have a metrics/analytics table with country of visit in a row. I have the following query:-

SELECT `country`, COUNT(`ip`) AS `cViews` FROM `metrics` WHERE `projID` = 'projID' AND `country` != 'XX' AND `when` BETWEEN '$dateFrom' AND '$dateTo' GROUP by country

Note; when is a timestamp field.

Which gets the number of visits per country from a given timeframe. Fine. Dandy, one might say.

What I now need, is to display a table (forget the displaying, I need help simply with the query) which can group the visits into each month under a title of that month. I.e. Country/Visits for each month. Probably specified with a timestamp of BETWEEN '2009-06-00' AND '2009-06-30' with those dates declared in variables at the top.

So I need to get from multiples of:-

`id`   `ip`  `country`   `etc`
 1     24.124    uk    thisthat

To:-

`country`     `Jan`     `Feb`
 UK             44       77

etc.

Now, I've had help here before which has declared names AS, but the potential complexity of this query is frigtening me and I'm not quite sure how to do it, even as nested select queries.

Any advice or pointing in the right direction gratefully received.

Answer 1

MySQL has a number of date and time functions, that you can use to parse a timestamp field, as well as to group by (ie. you want to count all records for a country in a specific month, so you'd group by country and year/month). For instance, if you grouped by the year and month of the when timestamp:

SELECT `country`, COUNT(`ip`) AS `cViews`,
  YEAR(`when`) AS `year`, MONTH(`when`) AS `month`
FROM `metrics`
WHERE `projID` = 'projID'
  AND `country` != 'XX'
  AND `when` BETWEEN '$dateFrom' AND '$dateTo'
GROUP BY country, YEAR(`when`), MONTH(`when`)

Your result set will look something like

country  cViews  year  month
UK       44      2009  7
UK       75      2009  6
 ...

And then you can use PHP to organize that data into the output format you desire.

Answer 2

The technique you are looking for (mapping the rows to the columns) is called pivoting.

Unfortunately, it's not what MySQL is good at, since it requires dynamic column count, and SQL programming paradigma implies static column count.

You can do the following:

SELECT  country,
        (
        SELECT  COUNT(*)
        FROM    metrics mjan
        WHERE   MONTH(mjan.`when`) = 1
                AND mjan.country = mo.country
        ) AS `Jan`,
        (
        SELECT  COUNT(*)
        FROM    metrics mfeb
        WHERE   MONTH(mfeb.`when`) = 2
                AND mfeb.country = mo.country
        ) AS `Feb`,
        …
FROM    metrics mo
GROUP BY
        country

, or, better, issue this query:

SELECT  country, MONTH('when'), COUNT(*)
FROM    metrics mo
GROUP BY
        country, MONTH(`when`)

and parse the results on php side