Skip to main content
R
RookOrn
Inspiring
December 9, 2007
Question

count

  • December 9, 2007
  • 3 replies
  • 464 views
Hi there,

I've got a database of website statistics. Within it i have a field with the time of each session.

I am aiming to find the most popular times of day, and to do so i want to count the number of users in each hour of the day.

I can get just the hour to display using #DATEFORMAT(xdate, "HH")#, however i can't seem to get a count of how many of each there are.

I've tried using 'count' in a query but it doesn't output the data in the way i need, which is:

Hour Count
12 6
13 6
14 9
15 3 etc...

Any help would be much appreciated.

Thanks

James
    This topic has been closed for replies.

    3 replies

    C
    cf_dev2
    Inspiring
    December 11, 2007
    To clarify, it sounds like you're retrieving all of the records from your table, then using CF to group and count them.

    If all you need is the total counts, it would be much more efficient to use your DB's date functions to return only the hour numbers and counts. But as Azadi mentioned, the exact date function syntax is db dependent.
    D
    dongzky
    Inspiring
    December 10, 2007
    maybe you can try something like this:

    <cfquery name="qryhour" ..... >
    SELECT DATEPART('hh',xdate) as nHour, Count(*) count_of_hours
    FROM table
    GROUP BY nHour
    ORDER BY nHour
    </cfquery>

    <cfoutput query="qryhour">
    #nHour# : #count_of_hours# <br />
    </cfoutput>

    Note: DATEPART function is for SQL. As Azadi said, look for a similar function in whatever db you're using. Example, for Oracle, the DATEPART() will become TO_CHAR(xdate,'HH24') as nHour.
    N
    Newsgroup_User
    Inspiring
    December 9, 2007
    which db?
    you will have to extract the hour from the field value and group by it
    in your query. to do that you will have to use a db-specific function
    like DATEPART or similar


    ---
    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices