You basically have data that you want to divide into buckets, with aggregate calculations performed on each bucket. One way to do this is to use a separate table to define the buckets, then JOIN against it to determine which bucket a date goes into, and GROUP BY the bucket so that aggregate calculations apply to the bucket.

Consider a table "bucket", which looks like: 

  +--------+------------+------------+
  | name   | begin      | end        |
  +--------+------------+------------+
  | Jan    | 2003-01-01 | 2003-01-31 |
  +--------+------------+------------+
  | ...                              |
  +--------+------------+------------+
  | Dec    | 2003-12-01 | 2003-12-30 |
  +--------+------------+------------+

[download]
Given this, you then issue a query like 
  SELECT contacts.state, bucket.name, count(*)
  FROM   contacts, bucket
  WHERE  contacts.indentifer BETWEEN bucket.begin AND bucket.end
  GROUP BY bucket.name, contacts.state
  ORDER BY contacts.state

[download]
This'll give you a set of (state, month, count) tuples that you might need to massage a bit before emitting as an HTML table. You may find it convenient to extend the query to use an outer join so that you'll get tuples for months with no contact events.

I haven't tested the query, but I do something similar in another context to divide data into buckets by date.

The nice thing about this approach is that you can reload the bucket table with other data to get different groupings. 

  +--------+------------+------------+
  | name   | begin      | end        |
  +--------+------------+------------+
  | 1Q03   | 2003-01-01 | 2003-03-31 |
  +--------+------------+------------+
  | ...                              |
  +--------+------------+------------+
  | 4Q03   | 2003-10-01 | 2003-12-30 |
  +--------+------------+------------+

[download]
will give you a breakdown by quarters, without changing the query.

In reply to Re: Selecting and ordering data over multiple date ranges by dws
in thread Selecting and ordering data over multiple date ranges by peppiv

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.