«
»

SQL chart query

Last week I wanted to make a small chart to display the number of users that signed up at Floorplanner per month. This is easily done by using query with a GROUP BY statement:

SELECT MONTH(created_on), COUNT(*)
  FROM users
  GROUP BY MONTH(created_on)

It returns a result with rows like this: [month number,number of accounts that month]. Since we have different kind of users (free and a couple of paid accounts) I thought it would be nice to put them in the equation too. After a while I came up with this query (actually not very different from the first one):

SELECT MONTH(created_on), account_type_id, COUNT(*)
  FROM users
  GROUP BY MONTH(created_on), account_type_id

Now it returns a result with rows like this: [month number, account type id, number of accounts that month for this type]. With some filtering I made it into a nice chart but I have the feeling that there is an easier solution.

Suggestions anyone?

This entry was posted on Friday, September 7th, 2007 at 8:17 am and is filed under Floorplanner. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “SQL chart query”

  1. Willem Says:
    December 18th, 2007 at 1:41 pm

    This is probably the best way to get the result you want. Note that this will only work for one year, otherwise you’ll be better off adding a YEAR(created_on) to your GROUP BY clause.

    I would however change the order of the GROUP BY clause en explicitly add an ORDER BY clause. This will force the result to have increasing month numbers for every account type. This makes it easier to check if all months are accounted for (months without a registration will be omitted from the result). Moreover, most charting libraries I have used seem to expect the input in this order if you want to project the month number on the x-axis.

Leave a Reply