Figure 1: Graph based on the data from the GROUP BY month, day, and year
A stat page can be used for an e-store, blog, ad rotater, or any other type of application where you want to track a specific series of "things" that happen each day. For example, on a blog you might track blog hits. In an e-store, you might track daily sales. For an ad rotator you might track click throughs. This article will show how to write a query to group the daily activity in the first section, and how to display that as a graph using ColdFusion in the second section. The article will use SQL Server syntax, but could be adapted to any database. Also, the SQL code is applicable to any server model -- not only ColdFusion. This is part 1 of 2.
For this article, I will assume you have a table that has an item that you need to break out into a daily statistic. The table should have one field that is a datetime field, recording a date/time that an event occurs. The event could be a page view, a click through, a sale of an item, or any other type of activity that would generate a row in your table. For the purposes of the article, I'll assume the following structure:
CREATE TABLE myItems (
myitemsid int IDENTITY (1, 1) NOT NULL ,
myItem varchar (50) NULL ,
myItemDate datetime NULL
)
ALTER TABLE myItems ADD
CONSTRAINT DF_myItems_myItemDate DEFAULT (getdate()) FOR myItemDate
and some sample data to start with:
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-10 5:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-10 2:20PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-10 3:30AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-09 12:01PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-09 10:03AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 6:00PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 5:30AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 12:07AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-08 1:00PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-08 3:00PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-07 3:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-07 4:00AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-06 7:31PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-05 9:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-04 9:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-04 8:31AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-04 8:39PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-03 7:32AM')
INSERT myItems (myItem,myItemDate) VALUES ('Test','2006-09-03 4:30PM')
INSERT myItems (myItem,myItemDate) VALUES ('Test2','2006-09-02 3:34PM')
The basic premise is that we want to build a graph or show a summary data list of the number of items on each day. We'll build the query next.
Using GROUP BY, you can create summaries of data. The following demonstrates a simply GROUP BY to determine the number of times different data was inserted into the myItem field:
SELECT myItem, COUNT(myItem) as TheCount
FROM myItems
GROUP BY myItem
ORDER BY COUNT(myItem) DESC
This gives us a result like this, using the data above:
| MyItem | TheCount |
|---|---|
| Test | 15 |
| Test2 | 5 |
How can we modify this to show data per day? You might try this:
SELECT myItem, myItemDate, COUNT(myItemDate) as TheCount
FROM myItems
GROUP BY myItem, myItemDate
ORDER BY COUNT(myItemDate) DESC
However, this gives you an undesirable result:
| myItem | myItemDate | TheCount |
|---|---|---|
| Test2 | 34:00.0 | 1 |
| Test | 32:00.0 | 1 |
| Test | 30:00.0 | 1 |
| Test | 31:00.0 | 1 |
| Test | 39:00.0 | 1 |
| Test | 30:00.0 | 1 |
| Test | 30:00.0 | 1 |
| Test2 | 31:00.0 | 1 |
| Test | 00:00.0 | 1 |
| Test | 30:00.0 | 1 |
| Test | 07:00.0 | 1 |
| Test | 30:00.0 | 1 |
| Test | 00:00.0 | 1 |
| Test2 | 00:00.0 | 1 |
| Test | 00:00.0 | 1 |
| Test2 | 03:00.0 | 1 |
| Test | 01:00.0 | 1 |
| Test | 30:00.0 | 1 |
| Test2 | 20:00.0 | 1 |
| Test | 30:00.0 | 1 |
That is because there is a time tagging along with the date -- you can't group your data based on date only unless you separate the date from the time. The following is one way to accomplish this, using the SQL functions MONTH, DAY, and YEAR:
SELECT myItem,
MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount
FROM myItems
GROUP BY myItem, DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate)
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)
Now, we get the result we want -- the data grouped by month, day, and year, with counts for each day:
| myItem | TheMonth | TheDay | TheYear | TheCount |
|---|---|---|---|---|
| Test2 | 9 | 2 | 2006 | 1 |
| Test | 9 | 3 | 2006 | 2 |
| Test | 9 | 4 | 2006 | 3 |
| Test | 9 | 5 | 2006 | 1 |
| Test2 | 9 | 6 | 2006 | 1 |
| Test | 9 | 7 | 2006 | 2 |
| Test | 9 | 8 | 2006 | 4 |
| Test2 | 9 | 8 | 2006 | 1 |
| Test | 9 | 9 | 2006 | 1 |
| Test2 | 9 | 9 | 2006 | 1 |
| Test | 9 | 10 | 2006 | 2 |
| Test2 | 9 | 10 | 2006 | 1 |
We could also eliminate the different values in the myItem field by simply leaving it out of the query:
SELECT
MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount
FROM myItems
GROUP BY DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate)
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)
This gives us a summary of the whole table by month, day, and year:
| TheMonth | TheDay | TheYear | TheCount |
|---|---|---|---|
| 9 | 2 | 2006 | 1 |
| 9 | 3 | 2006 | 2 |
| 9 | 4 | 2006 | 3 |
| 9 | 5 | 2006 | 1 |
| 9 | 6 | 2006 | 1 |
| 9 | 7 | 2006 | 2 |
| 9 | 8 | 2006 | 5 |
| 9 | 9 | 2006 | 2 |
| 9 | 10 | 2006 | 3 |
One last thing we need to do is to create a user-friendly "date" field, dropping the time. This is simply a matter of concatenating the day, month, and year, but first casting them into varchar data:
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4)) as TheFullDate
This composite field also has to be included in the GROUP BY clause. The full query now looks like this:
SELECT
MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount,
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4)) as TheFullDate
FROM myItems
GROUP BY DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate),
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4))
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)
To show this data as a simple bar graph, you can use the <cfchart> tag in ColdFusion. First, set up the <cfquery> tag:
<cfquery name="rsStats" datasource="#mydsn#">
SELECT
MONTH(myItemDate) AS TheMonth,
DAY(myItemDate) AS TheDay,
YEAR(myItemDate) AS TheYear,
COUNT(*) AS TheCount,
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4)) as TheFullDate
FROM myItems
GROUP BY DAY(myItemDate), MONTH(myItemDate), YEAR(myItemDate),
CAST (MONTH(myItemDate) as varchar(2)) + '/' +
CAST (Day(myItemDate) as varchar(2)) + '/' +
CAST(YEAR(myItemDate) as varchar(4))
ORDER BY YEAR(myItemDate), MONTH(myItemDate), DAY(myItemDate)
</cfquery>
Then, set up a <cfchart> tag:
<cfchart format="flash"
xaxistitle="Day"
yaxistitle="Hits Per Day"
chartwidth="600">
<cfchartseries type="bar"
query="rsStats"
itemcolumn="TheFullDate"
valuecolumn="TheCount" />
</cfchart>
If you browse the page, you will see a graph based on the data:
Figure 1: Graph based on the data from the GROUP BY month, day, and year
Summary queries are easy to write once you know a few basic tips for dealing with date/time data. Part one of this series showed a few basic techniques for grouping by date only. Part 2 will show one way to fill in some missing days, and how to dynamically adjust the scales on the graph based on how much data you have.